Q
Problem solve Get help with specific problems with your technologies, process and projects.

Tracing activity performed by vendor

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close