We must occasionally allow our support vendor to "dial in" to our system. I would like to trace all activity performed by the vendor. Is there a good way to start a trace automatically when a particular user signs in? I currently use the sys.dbms_system.set_sql_trace_in_session(SID, SerialNum, TRUE) procedure to manually activate the trace after the user has established his session. Automatic activation for particular internal users would also fall into this request.
I use a database trigger to perform such an action. The code of the trigger might look similar to the following:
CREATE OR REPLACE TRIGGER after_logon_trigger_extra AFTER LOGON ON DATABASE DECLARE usernm VARCHAR2(30); sid NUMBER; serial NUMBER; BEGIN SELECT user INTO usernm FROM dual; if (usernm = 'PEASLAND') THEN SELECT sid,serial# INTO sid,serial FROM v$session WHERE username=usernm; sys.dbms_system.set_sql_trace_in_session(sid,serial,TRUE); END IF; END; /
If the user PEASLAND connects to the database, then a trace is started. If a different user connects, nothing is done. After I am done with my tracing, I can then drop this database trigger so that PEASLAND doesn't always get tracing enabled.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.