Create a System Trigger to Enable SQL TRACE for a session

Create a System Trigger to Enable SQL TRACE for a session

The following script will enable session trace for the user scott.

[oracle@canada u01]$ cat trace_trigger.sql
set echo on

spool trace_trigger.log

DROP TRIGGER SYS.trace_trigger;

CREATE OR REPLACE TRIGGER sys.trace_trigger
After logon on database
Begin
            if ( user='SCOTT') then
                execute immediate 'alter session set sql_trace=true';
                execute immediate 'alter session set timed_statistics=true';
                execute immediate 'alter session set tracefile_identifier="SCOTT"';
                execute immediate 'alter session set max_dump_file_size=unlimited';
                execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
            End if;
End;
/

spool off;

SQL> select status from dba_triggers where trigger_name like'%TRACE_TRIGGER%';

STATUS
--------
ENABLED

To disable the trigger.

SQL> alter trigger trace_trigger disable;

Trigger altered.

SQL> select status from dba_triggers where trigger_name like'%TRACE_TRIGGER%';

STATUS
--------
DISABLED

To enable the trigger.

SQL> alter trigger trace_trigger enable;

Trigger altered.

SQL> select status from dba_triggers where trigger_name like'%TRACE_TRIGGER%';

STATUS
--------
ENABLED