Enable Current Session trace in Oracle Sqlplus

Enable Current Session trace in Oracle

Enable trace with Alter Command

1. For enable the current session trace

--with this statement identified of trace is easy
ALTER SESSION SET TRACEFILE_IDENTIFIER = "MY_TRACE_SQLPLUS";
ALTER SESSION SET sql_trace=TRUE;

2. Disable the current session trace

ALTER SESSION SET sql_trace=FALSE;

Enable trace with DBMS_SESSION package

1. For Enable trace

EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);

2. For Disable trace

EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);

Check the current trace file which generated for the session

SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

VALUE
---------------------------------------------------------------------
C:\ORACLE\diag\rdbms\xe\xe\trace\xe_ora_21520.trc

--Example with tracefile indentifier
ALTER SESSION SET TRACEFILE_IDENTIFIER = "MY_TRACE_SQLPLUS";
Session altered.

SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

VALUE
----------------------------------------------------------------------
C:\ORACLE\diag\rdbms\xe\xe\trace\xe_ora_21520_MY_TRACE_SQLPLUS.trc

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.