Instance and User level tracing in Oracle

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;
/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.