Another way to trace the session

In our previous blog posts, we have covered about tracing a specific session and also about event level tracing. In this blog post, we will demonstrate yet another way to trace the session using supplied dbms_session package. This is a very useful package to deal with session level details. For complete list of programs, please refer to Oracle PL/SQL Packages manual. In this post, we will just deal with two of its subprograms to enable and disable the tracing for a session from which procedure is invoked. Following is the signature of the procedure as also detailed in the Oracle 11g manual:

DBMS_SESSION.SESSION_TRACE_ENABLE(
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE,
plan_stat IN VARCHAR2 DEFAULT NULL);

By invoking this procedure, we can trace session both for wait events and for bind values. By default only wait events are traced. In order to trace the session for both bind variables and wait events, use following syntax.

SQL> EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits=>TRUE, binds=>TRUE);

PL/SQL procedure successfully completed.

In order to disable the trace, simply invoke session_trace_disable procedure.

SQL> EXEC DBMS_SESSION.SESSION_TRACE_DISABLE();

PL/SQL procedure successfully completed.

Trace file will be collected in the directory defined by user_dump_dest parameter.

Resources:

• Oracle 11g: PL/SQL Packages – here.