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;


[oracle@canada u01]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 22 06:04:31 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @trace_trigger.sql
SQL>
SQL> spool trace_trigger.log
SQL>
SQL> DROP TRIGGER SYS.trace_trigger;
DROP TRIGGER SYS.trace_trigger
*
ERROR at line 1:
ORA-04080: trigger 'trace_trigger' does not exist


SQL>
SQL> CREATE OR REPLACE TRIGGER sys.trace_trigger
  2  After logon on database
  3  Begin
  4              if ( user='SCOTT') then
  5                  execute immediate 'alter session set sql_trace=true';
  6                  execute immediate 'alter session set timed_statistics=true';
  7                  execute immediate 'alter session set tracefile_identifier="SCOTT"';
  8                  execute immediate 'alter session set max_dump_file_size=unlimited';
  9                  execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
 10              End if;
 11  End;
 12  /

Trigger created.

SQL>
SQL> spool off;
SQL>


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