Oracle常见问题

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)
    )
  )

 配置好后，PLSQL等就可以使用了。

 

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;

 

查询加锁的进程

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,

S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT 

FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ 

WHERE L.OBJECT_ID = O.OBJECT_ID 

AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR 

AND S.SQL_ADDRESS = SQ.ADDRESS;

杀死进程：

alter system kill session 'SID,SERIAL#';

 

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');

 

删除重复记录

delete from t where id not in ( select min(id) from t group by t.name)