前提
oracle中获取trace的方法有很多种
1.alter system set trace_enabled = true;(session)
2.set autotrace on
3.10046,10053 trace
4.dbms_system.set_sql_trace_in_session(sid,serial#,boolean) dbms_system package
5.select * from table(dbms_xplan.display_cursor('SQL ID',null,'advanced')); dbms_xplan package
本次讨论一下dbms_system package set_sql_trace_in_session procedure抓取trace的过程
dbms_system.set_sql_trace_in_session使用
查看一下包的描述,调用时要传入3个参数。
使用方法:
exec dbms_system.set_sql_trace_in_session(sid,serial#,boolean);
desc dbms_system
PROCEDURE SET_SQL_TRACE_IN_SESSION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL# NUMBER IN
SQL_TRACE BOOLEAN IN
查看当前session的sid
SQL> grant select any dictionary to user;
Grant succeeded.
SQL> sho user
USER is "SYS"
SQL> select * from v$mystat where rownum=1;
SID STATISTIC# VALUE CON_ID
---------- ---------- ---------- ----------
120 0 0 0
当前实例的inst_id
SQL> select instance_number,INSTANCE_NAME,HOST_NAME from v$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME
--------------- ---------------- ----------------------------------------------------------------
1 orcl19c1 rac1
获得trace路径的语句,会经常用到
查看单实例session trace路径
set linesize 160
column "trace file name" for a120
select pr.value || '/' || i.instance_name || '_ora_' || to_char(ps.spid) ||
'.trc' as "trace file name"
from v$session s, v$process ps, v$diag_info pr, v$instance i
where s.paddr = ps.addr
and s.sid = 120
and pr.name = 'Diag Trace