DBMS_MONITOR使用 (In 10g, 11g and Above)

SQL> desc dbms_monitor

Element                    Type      

-------------------------- --------- 

ALL_MODULES                CONSTANT  

ALL_ACTIONS                CONSTANT  

CLIENT_ID_STAT_ENABLE      PROCEDURE 

CLIENT_ID_STAT_DISABLE     PROCEDURE 

SERV_MOD_ACT_STAT_ENABLE   PROCEDURE 

SERV_MOD_ACT_STAT_DISABLE  PROCEDURE 

CLIENT_ID_TRACE_ENABLE     PROCEDURE 

CLIENT_ID_TRACE_DISABLE    PROCEDURE 

SERV_MOD_ACT_TRACE_ENABLE  PROCEDURE 

SERV_MOD_ACT_TRACE_DISABLE PROCEDURE 

SESSION_TRACE_ENABLE       PROCEDURE 

SESSION_TRACE_DISABLE      PROCEDURE 

DATABASE_TRACE_ENABLE      PROCEDURE 

DATABASE_TRACE_DISABLE     PROCEDURE 

You can query DBA_ENABLED_TRACES to determine what traces are enabled.

Example:
SQL> select trace_type, primary_id, QUALIFIER_ID1, waits, binds 
             from DBA_ENABLED_TRACES;

TRACE_TYPE                   PRIMARY_ID  QUALIFIER_ID1     WAITS    BINDS
----------------------           ---------------   ------------------      --------    -------
SERVICE_MODULE          SYS$USERS           SQL*Plus            TRUE     FALSE
CLIENT_ID                         HUGO                                                 TRUE     FALSE
SERVICE                             v101_DGB                                          TRUE      FALSE

At this database we have three different trace state.
1.) The first row shows that we trace on the server all SQL statements that are executed in SQL*Plus.
2.) We trace all session that are used in a session with the client identifier 'HUGO'
3.) We trace all program that are connected to the database via the service 'v101_DGB'.

In the next section we explain how we enable and disable tracing.

You can use the SESSION_TRACE_ENABLE procedure to enable SQL tracing for a given database session on the local instance.

Syntax
You enable tracing with
dbms_monitor.session_trace_enable(session_id => x, serial_num => y,
waits=>(TRUE|FALSE),binds=>(TRUE|FALSE) );
and disable tracing with
dbms_monitor.session_trace_disable(session_id => x, serial_num => y);
The default of waits is TRUE and the default of binds is FALSE.

Example
To get the session and the serial numbers you can query V$SESSION.

SQL> select serial#, sid , username from v$session; 

SERIAL#     SID   USERNAME
-------          -----  --------------
  1                 131
18                 139
  3                 140
 11                143     SCOTT

Than you can start tracing with the command
SQL> execute dbms_monitor.session_trace_enable(143,11);

This tracing state is not persistent across a database shutdown and you see no entry in DBA_ENABLED_TRACES.

SQL> select trace_type, primary_id, QUALIFIER_ID1, waits, binds 
            from DBA_ENABLED_TRACES;

no rows selected

As disconnect from the session or the following command stop tracing
SQL> execute dbms_monitor.session_trace_disable(143,11);