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_MY_TRACE_SQLPLUS.trc