如何在Oracle数据库10g中跟踪SQL
发布网友
发布时间：2022-04-23 18:21
共2个回答
懂视网
时间：2022-05-01 00:41
dbms_system是内部包，建议在官方指导下使用该包。
SQL> desc dbms_system PROCEDURE ADD_PARAMETER_VALUE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PARNAME VARCHAR2 IN VALUE VARCHAR2 IN SCOPE VARCHAR2 IN DEFAULT SID VARCHAR2 IN DEFAULT POSITION BINARY_INTEGER IN DEFAULT PROCEDURE DIST_TXN_SYNC Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- INST_NUM NUMBER IN PROCEDURE GET_ENV Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- VAR VARCHAR2 IN VAL VARCHAR2 OUT PROCEDURE KCFRMS PROCEDURE KSDDDT PROCEDURE KSDFLS PROCEDURE KSDIND Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- LVL BINARY_INTEGER IN PROCEDURE KSDWRT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- DEST BINARY_INTEGER IN TST VARCHAR2 IN PROCEDURE READ_EV Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- IEV BINARY_INTEGER IN OEV BINARY_INTEGER OUT PROCEDURE REMOVE_PARAMETER_VALUE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PARNAME VARCHAR2 IN VALUE VARCHAR2 IN SCOPE VARCHAR2 IN DEFAULT SID VARCHAR2 IN DEFAULT PROCEDURE REMOVE_PARAMETER_VALUE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PARNAME VARCHAR2 IN POSITION BINARY_INTEGER IN SCOPE VARCHAR2 IN DEFAULT SID VARCHAR2 IN DEFAULT PROCEDURE SET_BOOL_PARAM_IN_SESSION Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SID NUMBER IN SERIAL# NUMBER IN PARNAM VARCHAR2 IN BVAL BOOLEAN IN PROCEDURE SET_EV Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SI BINARY_INTEGER IN SE BINARY_INTEGER IN EV BINARY_INTEGER IN LE BINARY_INTEGER IN NM VARCHAR2 IN PROCEDURE SET_INT_PARAM_IN_SESSION Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SID NUMBER IN SERIAL# NUMBER IN PARNAM VARCHAR2 IN INTVAL BINARY_INTEGER IN PROCEDURE SET_SQL_TRACE_IN_SESSION Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SID NUMBER IN SERIAL# NUMBER IN SQL_TRACE BOOLEAN IN PROCEDURE WAIT_FOR_EVENT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- EVENT VARCHAR2 IN EXTENDED_ID BINARY_INTEGER IN TIMEOUT BINARY_INTEGER IN SQL>
#查看10046事件的追踪级别
SQL> declare lev integer; 2 begin 3 dbms_system.read_ev(‘10046‘,lev); 4 dbms_output.put_line(lev); 5 end; 6 / PL/SQL procedure successfully completed. SQL>
#对指定session开启sql_trace追踪
exec dbms_system.set_sql_trace_in_session(<sid>,<serial#>,true);---开启SQL_TRACE跟踪 exec dbms_system.set_sql_trace_in_session(<sid>,<serial#>,false);---关闭SQL_TRACE跟踪
#对指定session开启10046追踪
dbms_system.set_ev( si IN BINARY_INTEGER, -- session id se IN BINARY_INTEGER, -- session serial number ev IN BINARY_INTEGER, -- event number between 10000 and 10999 le IN BINARY_INTEGER, -- event level nm IN VARCHAR2); exec dbms_system.set_ev(<sid>,<serial#>,12,‘ybb‘); ----开启级别为12的跟踪 exec dbms_system.set_ev(<sid>,<serial#>,0,‘ybb‘); -----关闭跟踪
使用dbms_system追踪其它session
标签：
热心网友
时间：2022-04-30 21:49
开启会话跟踪：alter session set sql_trace=true;
关闭会话跟踪：alter session set sql_trace=false;
2. 启用 10046 事件跟踪全局 session
这将会对整个系统的性能产生严重的影响，所以一般不建议开启。
开启会话跟踪：alter system set events ‘10046 trace name context forever, level 12’;
关闭会话跟踪：alter system set events ‘10046 trace name context off’;
3. 使用 Oracle 系统包 DBMS_SYSTEM.SET_EV 跟踪指定 session
开启会话跟踪：SQL> exec dbms_system.set_ev(143, 112, 10046, 12, '');
关闭会话跟踪：SQL> exec dbms_system.set_ev(143, 112, 10046, 0, '');
