Packages

PROCEDURE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE

This procedure, which was introduced in Oracle 10.1, enables event 10046 trace for all sessions with the specified client ID.

The CLIENT_ID must be specified, but does not need to belong to a current session at the time this procedure is invoked.

There are two optional Boolean parameters, waits and binds, both of which default to FALSE.

The client identifier was introduced to provide a method of uniquely identifying sessions when many connect using the same Oracle user. This frequently occurs systems which include mid-tier servers or connection pools.

The client identifier can be set for a session using the SET_IDENTIFIER procedure in the DBMS_SESSION package. For example:

    EXECUTE DBMS_SESSION.SET_IDENTIFIER ('ID1');

The client identifier is externalised in the CLIENT_IDENTIFIER column of the V$SESSION dynamic performance view. For example:

    SELECT sid FROM v$session WHERE client_identifier = 'ID1';

For example to enable event 10046, level 1 trace for CLIENT_ID 'ID1' use:

    EXECUTE dbms_monitor.client_id_trace_enable (client_id=>'ID1');

To enable event 10046, level 4 trace (bind variables) for CLIENT_ID 'ID1' use:

    EXECUTE dbms_monitor.client_id_trace_enable (client_id=>'ID1',binds=>true);

To enable event 10046, level 8 trace (waits) for CLIENT_ID 'ID1' use:

    EXECUTE dbms_monitor.client_id_trace_enable (client_id=>'ID1',waits=>true);

The current state of client_id trace is reported in the data dictionary view DBA_ENABLED_TRACES. This contains the following columns:

For client_id trace, the TRACE_TYPE column will be 'CLIENT_ID' and the PRIMARY_ID column will contain the client identifier.

The DBA_ENABLED_TRACES view is based on the underlying data dictionary table SYS.WRI$_TRACING_ENABLED.