Oracle - Using SQL Trace Triggered by User Login

From ExxtremeWiki
Jump to: navigation, search
/*
** Turn on SQL Tracing - check init.ora for location of file
	http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4254158186083
*/
 
CREATE TRIGGER logon_trigger
after logon ON schema
  BEGIN
       EXECUTE immediate
   'alter session set events ''10046 trace name context forever, level 12''';
    END;
/
 
/*
	After exiting, I can find the trace file (in the user_dump_destination -- an init.ora
parameter).  and in that trace file, we'll see:
*/
 
 
/*
	And finally to stop this from happening on each logon:
*/
 
DROP TRIGGER logon_trigger
;

--Will (talk) 14:36, 17 May 2013 (CDT)--Will (talk) 09:23, 8 May 2013 (CDT)