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