本文共 2258 字,大约阅读时间需要 7 分钟。
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---------- ---------- ------------------- 2845 1649 2018-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_name FROM (SELECT p.spid FROM v$mystat m, v$session s, v$process p WHERE m.statistic# = 1 AND s.SID = &SID AND p.addr = s.paddr) p, (SELECT t.INSTANCE FROM v$thread t, v$parameter v WHERE v.NAME = 'thread' AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i, (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d SQL> SELECT d.VALUE || '\' || LOWER (RTRIM (i.INSTANCE, CHR (0))) || '_ora_' || p.spid || '.trc' trace_file_name FROM (SELECT p.spid FROM v$mystat m, v$session s, v$process p WHERE 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.INSTANCE FROM v$thread t, v$parameter v WHERE v.NAME = 'thread' AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i, (SELECT VALUE 11 12 13 14 15 16 FROM v$parameter WHERE 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
转载地址:http://rchji.baihongyu.com/