This content has been marked as final. Show 5 replies
"How to Trace Specific Database Users to Collect Full DML Statements Executed [ID 309798.1]"
How To Trace User Session Details [ID 489589.1]
The first one suggests to create an AFTER LOGON trigger for enabling trace for a particular user when ever it logs in !
But I think it will case unnecessary trace generation also when several connections are being made using a single user. Isn't it ?
did you consider audit as option ?
No no, I want to see what all queries the reporting session executes and the performance stats. That's why trace is needed.
I would suggest to trace session 12 here. Along with this you should keep serial# also in v$session query as sid,serial# identifies the uniqueness of the db session
Apart from this you may trace the sessions based on client_identifier of v$session using DBMS_MONITOR
If you client is not supplying client_id to session & its null then you can use different approach to trace the sessions e.g. module,action etc.
Use of DBMS_MONITOR based on client_identifier :
Logon trigger to trace all connections connecting to DB , you may make some changes in code to consider required sessions only
SQL> exec dbms_session.set_identifier('<client_id>'); SQL> alter session set timed_statistics = true; SQL> alter session set max_dump_file_size = unlimited; SQL> alter session set tracefile_identifier = '<client_id>'; SQL> exec dbms_monitor.client_id_trace_enable(client_id=>'<client_id>');
CREATE OR REPLACE TRIGGER trace AFTER logon ON database begin execute immediate 'Alter session set tracefile_identifier=''apps_traces'''; execute immediate 'Alter session set events ''10046 trace name context forever, level 16'''; end; /