1.安装dbms_support包
SQL> @?/rdbms/admin/dbmssupp.sqlPackage created.Package body created.SQL> grant execute on dbms_support to dairy;
2.找到需要跟踪的会话
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';Session altered.SQL> select sid,serial#,logon_time from v$session where audsid=userenv('sessionid');SID SERIAL# LOGON_TIME---------- ---------- -------------------28451649 -10-18 09:50:40
3.使用start_trace_in_session进行跟踪
SQL> exec dbms_support.start_trace_in_session(2845,1649,TRUE,TRUE);PL/SQL procedure successfully completed.
4.开始跟踪后，在客户端进行对应的操作。
5.使用stop_trace_in_session结束跟踪
.
SQL> exec dbms_support.stop_trace_in_session(2845,1649);PL/SQL procedure successfully completed
6.使用以下的语句获得trace文件的位置。
SELECT d.VALUE|| '\'|| LOWER (RTRIM (i.INSTANCE, CHR (0)))|| '_ora_'|| p.spid|| '.trc'trace_file_nameFROM (SELECT p.spidFROM v$mystat m, v$session s, v$process pWHERE m.statistic# = 1 AND s.SID = &SID AND p.addr = s.paddr) p,(SELECT t.INSTANCEFROM v$thread t, v$parameter vWHEREv.NAME = 'thread'AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,(SELECT VALUEFROM v$parameterWHERE NAME = 'user_dump_dest') dSQL> SELECT d.VALUE|| '\'|| LOWER (RTRIM (i.INSTANCE, CHR (0)))|| '_ora_'|| p.spid|| '.trc'trace_file_nameFROM (SELECT p.spidFROM v$mystat m, v$session s, v$process pWHERE m.statisti 2 3 4 5 6 7 8 9 10 c# = 1 AND s.SID = '2845' AND p.addr = s.paddr) p,(SELECT t.INSTANCEFROM v$thread t, v$parameter vWHEREv.NAME = 'thread'AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,(SELECT VALUE11 12 13 14 15 16 FROM v$parameterWHERE NAME = 'user_dump_dest') d 17 ;TRACE_FILE_NAME--------------------------------------------------------------------------------/home/u01/app/oracle/diag/rdbms/foidndb/foidndb/trace\foidndb_ora_19143.trc
7.使用TKPROF将trace文件转换成为执行计划解析的文本。
[oracle@foidndb db_1]$ tkprof /home/u01/app/oracle/diag/rdbms/foidndb/foidndb/trace/foidndb_ora_19143.trc /u01/test.txt
