从10g开始，可以使用dbms_monitor开启/关闭会话跟踪。
sql> desc dbms_monitorprocedure client_id_stat_disable argument name type in/out default? ------------------------------ ----------------------- ------ -------- client_id varchar2 inprocedure client_id_stat_enable argument name type in/out default? ------------------------------ ----------------------- ------ -------- client_id varchar2 inprocedure client_id_trace_disable argument name type in/out default? ------------------------------ ----------------------- ------ -------- client_id varchar2 inprocedure client_id_trace_enable argument name type in/out default? ------------------------------ ----------------------- ------ -------- client_id varchar2 in waits boolean in default binds boolean in default plan_stat varchar2 in defaultprocedure database_trace_disable argument name type in/out default? ------------------------------ ----------------------- ------ -------- instance_name varchar2 in defaultprocedure database_trace_enable argument name type in/out default? ------------------------------ ----------------------- ------ -------- waits boolean in default binds boolean in default instance_name varchar2 in default plan_stat varchar2 in defaultprocedure serv_mod_act_stat_disable argument name type in/out default? ------------------------------ ----------------------- ------ -------- service_name varchar2 in module_name varchar2 in action_name varchar2 in defaultprocedure serv_mod_act_stat_enable argument name type in/out default? ------------------------------ ----------------------- ------ -------- service_name varchar2 in module_name varchar2 in action_name varchar2 in defaultprocedure serv_mod_act_trace_disable argument name type in/out default? ------------------------------ ----------------------- ------ -------- service_name varchar2 in module_name varchar2 in default action_name varchar2 in default instance_name varchar2 in defaultprocedure serv_mod_act_trace_enable argument name type in/out default? ------------------------------ ----------------------- ------ -------- service_name varchar2 in module_name varchar2 in default action_name varchar2 in default waits boolean in default binds boolean in default instance_name varchar2 in default plan_stat varchar2 in defaultprocedure session_trace_disable argument name type in/out default? ------------------------------ ----------------------- ------ -------- session_id binary_integer in default serial_num binary_integer in defaultprocedure session_trace_enable argument name type in/out default? ------------------------------ ----------------------- ------ -------- session_id binary_integer in default serial_num binary_integer in default waits boolean in default binds boolean in default plan_stat varchar2 in defaultsql>
1.会话级设置跟踪
#找到会话sidselect sid,serial#,username from v$session;#开启跟踪（备注：四个参数都是可选参数，不带任何参数跟踪的是当前会话，跟踪结束后要关闭跟踪）exec dbms_monitor.session_trace_enable(session_id=>190,serial_num=>8351,waits=>true,binds=>true);#检查会话是否被跟踪select sql_trace,sql_trace_waits,sql_trace_binds from v$session where sid=190;#关闭跟踪exec dbms_monitor.session_trace_disable(session_id=>190,serial_num=>8351);
2.根据客户端标识设置会话跟踪
#设置客户端标识符SQL> exec dbms_session.set_identifier('abce_id');SQL> select sid,serial#,client_identifier from v$session where client_identifier is not null; SID SERIAL# CLIENT_IDENTIFIER---------- ---------- ---------------------------------------- 190 8355 abce_idSQL> #客户端标识符设置跟踪SQL> exec dbms_monitor.client_id_trace_enable(client_id=>'abce_id',waits=>true,binds=>false);SQL> select primary_id as client_id,waits,binds from dba_enabled_traces where trace_type='CLIENT_ID';CLIENT_ID WAITS BINDS---------------------------------------------------------------- ----- -----abce_id TRUE FALSESQL> #关闭跟踪SQL> exec dbms_monitor.client_id_trace_disable(client_id=>'abce_id');
3.模块级、数据库级监控(略，具体查看上面的定义)
4.跟踪视图
查看dba_enabled_traces和dba_enabled_aggregations视图，可以看到启用的跟踪和收集的统计信息。可以使用这些视图确保已经禁用的所有跟踪选项。