使用dbms_system追踪其它session

dbms_system是内部包，建议在官方指导下使用该包。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85

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事件的追踪级别

?

1 2 3 4 5 6 7 8 9 10

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追踪

?

1 2	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追踪

?

1 2 3 4 5 6 7 8 9

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');    -----关闭跟踪