Loggon Trigger for Tracing

Using the same technique I mentioned at "Tracing Commands" here is a code in a form of logging trigger, easier when you want to capture just a specific schema from the very first transaction.

CREATE OR REPLACE TRIGGER SYS.LOGON_TRACE_CRYSTAL_TRG
AFTER LOGON
ON CRYSTAL.SCHEMA
DECLARE
cmd VARCHAR2(100);
BEGIN
cmd := 'ALTER SESSION SET max_dump_file_size = unlimited';
EXECUTE IMMEDIATE cmd;
cmd := 'ALTER SESSION SET tracefile_identifier = ''10046''';
EXECUTE IMMEDIATE cmd;
cmd := 'ALTER SESSION SET statistics_level = ALL';
EXECUTE IMMEDIATE cmd;
cmd := 'ALTER SESSION SET events ''10046 trace name context forever, level 12''';
EXECUTE IMMEDIATE cmd;
EXCEPTION
WHEN OTHERS THEN
--NULL;
RAISE;
END ;
/

optional:

CREATE OR REPLACE SYS.TRIGGER LOGOFF_TRACE_CRYSTAL_TRG
BEFORE LOGOFF
ON CRYSTAL.SCHEMA
DECLARE
cmd VARCHAR2(100);
BEGIN
cmd := 'ALTER SESSION SET EVENTS ''10046 trace name context off''';
EXECUTE IMMEDIATE cmd;
EXCEPTION
WHEN OTHERS THEN
--NULL;
RAISE;
END ;
/

Comments

Popular posts from this blog

Fixing & Registering ORACLE_HOMES in Central Inventory

Export in Pl/Sql via DBMS_DATAPUMP