Search This Blog

Total Pageviews

Thursday, 17 June 2010

oracle trace via database logon trigger

Database trigger


-- GRANT administer DATABASE TRIGGER TO "SCOTT";


CREATE OR REPLACE TRIGGER ON_LOGON_CCCPROD AFTER LOGON ON DATABASE
WHEN ( USER = 'SCOTT' )

declare

v_trace_file_name varchar2(40);

-- LOWER(SYS_CONTEXT('userenv', 'session_user'))||to_char(sysdate,'dd.mm.yyyyhh:mi')
--

BEGIN
v_trace_file_name:= replace(replace(LOWER(SYS_CONTEXT('userenv', 'session_user')),'-', '_'), '''' , '_')||to_char(sysdate,'dd.mm.yyyyhh:mi') ;

EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER='''|| v_trace_file_name||'''';
-- execute immediate ;
execute immediate 'alter session set timed_statistics=true';
execute immediate 'alter session set max_dump_file_size=unlimited';
execute immediate 'alter session set STATISTICS_LEVEL = ALL' ;
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

No comments:

Oracle DBA

anuj blog Archive