Create trace using DBMS_MONITOR

DBMS_MONITOR can be used to trace sessions or make traces with larger scope.
First let’s have a look to the available procedures of DBMS_MONITOR. Each procedure exists twice, one to enable the trace and one to disable it.

The CLIENT_ID_TRACE procedure

This procedure is used to enable tracing using a Client Identifier.

This means that all sessions using the same Client Identifier will be traced at the same time. This type of tracing is useful when you have to make a trace for an application using an application pool to connect to the database.

The trace will persist after a database restart and you’ll have to explicitly disable it.

DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(

 client_id    IN  VARCHAR2,

 waits        IN  BOOLEAN DEFAULT TRUE,

 binds        IN  BOOLEAN DEFAULT FALSE,

 plan_stat    IN  VARCHAR2 DEFAULT NULL);

Example

## set the identifier in the session you want to trace

SQL> exec DBMS_SESSION.SET_IDENTIFIER('this is a test');

PL/SQL procedure successfully completed.

## Then enable the trace for the client identifier, execute the following command in another session:

SQL>  exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('this is a test',true,true);

PL/SQL procedure successfully completed.

## To see if the trace is enabled, you can query the dba_enabled_trace view :

SQL> select TRACE_TYPE,PRIMARY_ID,WAITS,BINDS,PLAN_STATS from dba_enabled_traces;

TRACE_TYPE            PRIMARY_ID                     WAITS BINDS    PLAN_STATS

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

CLIENT_ID             TRUE                                      TRUE  FIRST_         EXEC

You can choose to get the waits, binds and plan_stats using the parameters.
## Don’t forget to disable the trace when done:

SQL> exec DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE('this is a test');

PL/SQL procedure successfully completed.

The DATABASE_TRACE procedure

Description

This procedure can be used to enable the trace for the whole database or for a specific instance in case RAC is used.

DBMS_MONITOR.DATABASE_TRACE_ENABLE(

   waits          IN BOOLEAN DEFAULT TRUE,

   binds          IN BOOLEAN DEFAULT FALSE,

   instance_name  IN VARCHAR2 DEFAULT NULL,

   plan_stat      IN VARCHAR2 DEFAULT NULL);

## To activate the trace:

exec dbms_monitor.database_trace_enable(waits=>true,binds=>TRUE);

PL/SQL procedure successfully completed.

## To see if the trace is activated :

SQL> select TRACE_TYPE,PRIMARY_ID,WAITS,BINDS,PLAN_STATS from dba_enabled_traces;

TRACE_TYPE            PRIMARY_ID                WAITS BINDS   PLAN_STATS

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

DATABASE                            TRUE                            TRUE     FIRST_EXEC

## Then disable the trace when done:

SQL> exec dbms_monitor.database_trace_disable;

PL/SQL procedure successfully completed.

SERV_MOD_ACT_TRACE procedure

Description

This procedure can be used to trace a particular application. It is possible to filter more finely application specifying the type of action you want to trace

dbms_monitor.serv_mod_act_trace_enable(

service_name  IN VARCHAR2,

module_name   IN VARCHAR2 DEFAULT ANY_MODULE,

action_name   IN VARCHAR2 DEFAULT ANY_ACTION,

waits         IN BOOLEAN  DEFAULT TRUE,

binds         IN BOOLEAN  DEFAULT FALSE,

instance_name IN VARCHAR2 DEFAULT NULL,

plan_stat     IN VARCHAR2 DEFAULT NULL);

## Consider you want to trace every user using SQL*PLUS, you can use this command:

exec dbms_monitor.serv_mod_act_trace_enable('SYS$USERS', 'SQL*Plus', dbms_monitor.all_actions, TRUE, TRUE);

## to see if trace is enabled:

SQL> select TRACE_TYPE,PRIMARY_ID,QUALIFIER_ID1, QUALIFIER_ID2, WAITS,BINDS from dba_enabled_traces;

TRACE_TYPE            PRIMARY_ID        QUALIFIER_ID1                             QUALIFIER_ID2        WAITS BINDS

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

SERVICE_MODULE        SYS$USERS           SQL*Plus                                                                          TRUE                           TRUE

## To disable it :

SQL>  exec dbms_monitor.serv_mod_act_trace_disable('SYS$USERS', 'SQL*Plus', dbms_monitor.all_actions);

PL/SQL procedure successfully completed.

SESSION_TRACE Procedure

Description

The SESSION_TRACE procedure is used to trace a specific session using the SID and SERIAL# columns of the v$session view. Like the other procedures you can use parameters to collect binds and waits.

DBMS_MONITOR.SESSION_TRACE_ENABLE(

    session_id   IN  BINARY_INTEGER DEFAULT NULL,

    serial_num   IN  BINARY_INTEGER DEFAULT NULL,

    waits        IN  BOOLEAN DEFAULT TRUE,

    binds        IN  BOOLEAN DEFAULT FALSE,

    plan_stat    IN  VARCHAR2 DEFAULT NULL);

##  To disable the trace

SQL> exec DBMS_MONITOR.SESSION_TRACE_ENABLE(5,14,true,true);

PL/SQL procedure successfully completed.