Systems Engineering and RDBMS

Another way to trace the session

Posted by decipherinfosys on March 28, 2009

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: