Instance and User level tracing in Oracle

Enable Instance level tracing in Oracle

-- Enable the trace at instance level
alter system set events '10046 trace name context forever,level 12';
-- Disable the trace at instance level
alter system set events '10046 trace name context off';

Initialization parameter setting for instance level tracing
You can defined event in init file for enable tracing at instance level.

-- Entry the parameter in init file and start the database
event="10046 trace name context forever,level 12"
-- Disable the trace at instance level
alter system set events '10046 trace name context off';


Enable user level tracing with Logon Trigger
When use connected with the database it make connection then logon trigger will fire automatic and enable the trace for that session.

CREATE OR REPLACE TRIGGER SYS.set_trace
AFTER LOGON ON DATABASE
WHEN (USER like '&USERNAME')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger''';
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END set_trace;
/