Oracle常见问题

最新推荐文章于 2022-10-13 16:22:43 发布

川川Jason

最新推荐文章于 2022-10-13 16:22:43 发布

阅读量390

点赞数

分类专栏： Database 文章标签： Oracle Instant Client 重置Sequence 重建索引 Primary Key Conflict

本文链接：https://blog.csdn.net/bansan/article/details/84361223

版权

Database 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

创建账户

Oracle 12C(2013年发布)后采用多租户架构（Multitenant architecture），引入了CDB（container database）与PDB（pluggable database），12.2后引入Application Container，一个CDB可以承载多个PDB或Application Container。

默认安装后会创建一个ORCL实例、一个CDB和一个ORCLPDB(12.2后)，Application Container是可选的。

查询当前可用的service：
SELECT name, pdb FROM v$services ORDER BY name;

查看当前的Container:
SHOW CON_NAME;
或
SELECT SYS_CONTEXT('USERENV', 'CON_NAME') FROM dual;

切换Container：
ALTER SESSION SET CONTAINER=orclpdb;
ALTER SESSION SET CONTAINER=cdb$root;

查询PDB：
select name,open_mode from v$pdbs;

启/停PDB(以sysdba权限登录)：
ALTER PLUGGABLE DATABASE orclpdb open;
ALTER PLUGGABLE DATABASE orclpdb close;

以指定模式启动PDB：
ALTER PLUGGABLE DATABASE orclpdb OPEN READ WRITE;
ALTER PLUGGABLE DATABASE orclpdb OPEN READ ONLY;

启动或者关闭所有PDB：
ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

注意，服务器重启后，PDB默认不启动。

查询PDB文件：

SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
FROM DBA_PDBS p, CDB_DATA_FILES d
WHERE p.PDB_ID = d.CON_ID
ORDER BY p.PDB_ID;

12c后Oracle账户分为通用(Common)和本地(Local)两种类型，12.2后Common用户又分为CDB common user 和 application common user。

可执行以下语句查询用户：
select username,common,con_id from cdb_users order by common, username;

创建CDB通用用户，必须登录或切换到CDB容器，用户名必须以C##或者c##开头，指定或不指定CONTAINER=ALL均可：

CONNECT SYSTEM
Enter password: password
Connected.

CREATE USER c##hr_admin
IDENTIFIED BY password
DEFAULT TABLESPACE data_ts
QUOTA 100M ON test_ts
QUOTA 500K ON data_ts
TEMPORARY TABLESPACE temp_ts
CONTAINER = ALL;

GRANT SET CONTAINER, CREATE SESSION TO c##hr_admin 
CONTAINER = ALL;

如用户名未以C##或者c##开头，则会报错：ORA-65096: 公用用户名或角色名无效。

如果不想以C##或者c##开头，可以先执行以下语句再创建用户（以下划线开头的参数为Oracle内部参数，存在风险，不推荐使用）：
alter session set "_oracle_script" = true;

application container是可选的，有多种方式创建application container，以克隆orclpdb为例，方法如下：

CREATE PLUGGABLE DATABASE sales_app AS APPLICATION CONTAINER FROM orclpdb
  FILE_NAME_CONVERT = ('D:\ORACLE\ORADATA\ORCL\ORCLPDB\', 'D:\ORACLE\ORADATA\ORCL\SALES_APP\')
  STORAGE (MAXSIZE 2G);

ALTER PLUGGABLE DATABASE sales_app OPEN READ WRITE;

创建application common user，必须登录/切换到app容器，如下：

alter session set container=sales_app;

CREATE USER app_admin
IDENTIFIED BY password
DEFAULT TABLESPACE data_ts
QUOTA 100M ON temp_ts
QUOTA 500K ON data_ts
TEMPORARY TABLESPACE temp_ts
CONTAINER = ALL;

GRANT SET CONTAINER, CREATE SESSION TO app_admin CONTAINER = ALL;

创建本地用户，必须登录或切换到PDB容器，指定或不指定CONTAINER=CURRENT均可：

CONNECT SYSTEM@orclpdb
Enter password: password
Connected.

CREATE USER jason
 IDENTIFIED BY password
 DEFAULT TABLESPACE data_ts
 QUOTA 100M ON test_ts
 QUOTA 500K ON data_ts
 TEMPORARY TABLESPACE temp_ts
 PROFILE hr_profile
 CONTAINER = CURRENT;

Oracle Instant Client

Instant Client是轻量级的Oracle数据库客户端，安装程序为一个ZIP包，下载解压后，配置ORACLE_HOME、TNS即可使用。ORACLE_HOME为解压后的路径，在其下创建network\admin目录，然后创建tnsnames.ora文件，内容如下：

sample =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sample)
    )
  )

ORA-01502 重建索引
Begin
For v_index In (Select index_name As iname
                    From User_Indexes
                   Where status = 'UNUSABLE') Loop
    Execute Immediate 'alter index ' || v_index.Iname || ' rebuild';
End Loop;
End;

重置Sequence
DECLARE
   table_name           VARCHAR2 (30) := 'JBPM_BYTEARRAY';
   id_col_name          VARCHAR2 (30) := 'ID_';
   sequence_name    VARCHAR2 (30) := 'HIBERNATE_SEQUENCE';
   max_id          NUMBER (10);
   diff                NUMBER (10);
   str_sql         VARCHAR2 (100);
BEGIN
   str_sql := 'select max(' || id_col_name || ') from ' || table_name;
   execute immediate str_sql into max_id;

   str_sql := 'select ' || max_id || ' - ' || sequence_name || '.nextval from dual';
   execute immediate str_sql into diff;

   str_sql := 'alter sequence ' || sequence_name || ' increment by ' || (diff + 2);
   execute immediate str_sql;
   str_sql := 'select ' || sequence_name || '.nextval from dual';
   execute immediate str_sql;

   str_sql := 'alter sequence ' || sequence_name || ' increment by 1';
   execute immediate str_sql;
END;

Primary Key Conflict
通过expdp备份数据库还原后，经常发生primary key conflict，为避免这种问题，在导出时加上flashback_time参数。
flashback_time=systimestamp
或
flashback_time="to_timestamp('02-08-2014 10:10:00', 'DD-MM-YYYY HH24:MI:SS')"

a. The behavior of Data Pump Export utility is different from original Export utility in terms of how the active transactions are treated.
b. The original Export utility, when setting consistent=y, will create export dump file of database objects from the point in time at the beginning of the Export session.
c. Without setting values for FLASHBACK_SCN or FLASHBACK_TIME, Data Pump Export utility may create an inconsistent export.
d. To insure a consistent export with Data Pump export, either set the FLASHBACK_SCN or FLASHBACK_TIME parameter, or restart the database in restrict mode before the export session starts.
Data Pump 10g / 11g Flashback_Scn and flashback_Time parameter

查看列信息
SELECT * FROM USER_TAB_COLUMNS where COLUMN_NAME = 'col_name';

查看TABLE、VIEW、INDEX、SEQUENCE、PACKAGE等对象信息
select * from user_objects where object_type = 'TABLE' and object_name = 'object_name';

DBA_TABLES、ALL_TABLES、USER_TABLES的关系和区别

三个视图都可以用来查询表信息，DBA_TABLES >= ALL_TABLES >= USER_TABLES
DBA_TABLES为DBA拥有的或可以访问的所有表。
ALL_TABLES为某一用户拥有的或可以访问的所有表。
USER_TABLES为某一用户所拥有的所有表。

由上可知，当某一用户本身为数据库DBA时，DBA_TABLES与ALL_TABLES等价。

dba_tables、user_all_tables中num_rows的值和表count(*)不一致原因
num_rows、blocks等一些信息是使用analyze table分析表时填写进去的，命令格式如下：
analyze table tabname compute statistics;
或
call dbms_stats.gather_table_stats(ownname, tabname);

从Oracle 10g开始，Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任务，用于自动收集CBO(Cost Based Optimization)的统计信息。这个任务默认情况下在工作日晚上10：00 - 6：00和周末全天开启（同在10点运行的Job还有一个AUTO_SPACE_ADVISOR_JOB）。调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集统计信息。该过程首先检测统计信息缺失和陈旧的对象，然后确定优先级，再开始进行统计。

说明：当做完统计信息后，如果对象的行数修改达到10%，DBMS_STATS就认为是统计信息过旧。

可以通过以下SQL查询这个JOB的运行情况：
SQL> select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB'

然而这个自动化功能影响了很多系统的正常运行，晚上10点对于大部分生产系统并非空闲时段。而自动分析可能导致极为严重的闩锁竞争，进而可能导致数据库Hang或者Crash。

关闭及开启自动搜集功能，有两种方法，分别如下：
方法一：
exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');
方法二：
alter system set "_optimizer_autostats_job"=false scope=spfile;
alter system set "_optimizer_autostats_job"=true scope=spfile;

修改日期显示格式
修改当前会话日期格式
    alter session set NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
修改初始化参数
    alter system set nls_date_format='yyyy-mm-dd' SCOPE=spfile;
修改客户端注册表
    在HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE下，新建字符串值nls_date_format=yyyy-mm-dd hh24：mi：ss

查询本地化参数
与本地化参数有关的数据字典有：
    V$NLS_PARAMETERS
    V$PROPS$
    NLS_DATABASE_PARAMETERS
    NLS_INSTANCE_PARAMETERS
    NLS_SESSION_PARAMETERS
执行以下任一语句均可：
    show parameters;
    select * from V$NLS_PARAMETERS;
    select * from V$PROPS$;
    select * from nls_session_parameters;

插入换行符
换行符 chr(10)               回车符 chr(13)
insert into t(col) values('hello'||chr(10)||'coco');