ORACLE 日常诊断工具

1.SQL跟踪

(1)10046

SQL>  alter session set events '10046 trace name context forever,level 12';

SQL>  alter session set events '10046 trace name context off';

(2)跟踪其它会话

exec dbms_system.set_sql_trace_in_session(7,184,true);
exec dbms_system.set_sql_trace_in_session(7,184,false);

(3)跟踪当前会话

SQL> ALTER SESSION SET sql_trace=TRUE;
SQL> ALTER SESSION SET sql_trace=FALSE;

2.errorstack

SQL> alter session set events '12899 trace name errorstack forever,level 3';

Session altered.

SQL> insert into a values('cccc');
insert into a values('cccc')
                     *
ERROR at line 1:
ORA-12899: value too large for column "AIKI"."A"."I" (actual: 4, maximum: 2)

SQL> alter session set events '12899 trace name errorstack off';

3.缓冲区dump--如果用户没有相关权限，在UDUMP下不能生成相关文件

SQL> alter session set events 'immediate trace name heapdump level 2';

Session altered.

4.logmnr

SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/kfc/redo03.log',dbms_logmnr.new);
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

create table ta_logmnr  as select * from v$logmnr_contents;

SQL> exec dbms_logmnr.end_logmnr;

5.10053

SQL> alter session set events='10053 trace name context forever,level 1';

Session altered.

SQL> select count(*) from t1;

  COUNT(*)
----------
     50000

SQL>  alter session set events='10053 trace name context off';