Friday, September 14, 2012

How to Trace Another User's Session

This was tested on Oracle 11.2. A user connected to a database like this:
 
SQL> conn andrew/reid
Connected.
SQL>
 
I logged in as SYS and noted his SID and SERIAL# in V$SESSION:
 
SQL> show user
USER is "SYS"
SQL> select sid, serial#
  2  from v$session
  3  where username = 'ANDREW';

       SID    SERIAL#
---------- ----------
       298        353

SQL>
 
Then I used them to trace his session:
 
SQL> exec dbms_system.set_sql_trace_in_session -
> (298,353,true);
 
PL/SQL procedure successfully completed.
 
SQL>
 
He ran some SQL:
 
SQL> show user
USER is "ANDREW"
SQL> select sysdate from dual;

SYSDATE
---------
13-SEP-12

SQL>
 
I stopped tracing his session and checked the value of diagnostic_dest and user_dump_dest:
 
SQL> show user
USER is "SYS"
SQL> exec dbms_system.set_sql_trace_in_session -
> (298,353,false);

PL/SQL procedure successfully completed.

SQL> l
  1  select value from v$parameter
  2* where name = 'diagnostic_dest'
SQL> /

VALUE
----------------------------------------------------------------------
/oracle/app/oracle/product

SQL> select value from v$parameter
  2  where name = 'user_dump_dest';

VALUE
----------------------------------------------------------------------
/oracle/app/oracle/product/diag/rdbms/busdpt1/BUSDPT1/trace

SQL>
 
At this stage I still don’t fully understand how these fit together in Oracle 11. I will do some research and publish it in a separate post when I do. Anyway, I found the trace file in the location pointed to by user_dump_dest and it contained the SQL which the user had run. Notice how the SESSION ID line includes the user's SID and SERIAL#:
 
BUSDPT1 /oracle/app/oracle/product/diag/rdbms/busdpt1/BUSDPT1/trace > cat BUSDPT1_ora_4622.trc
Trace file /oracle/app/oracle/product/diag/rdbms/busdpt1/BUSDPT1/trace/BUSDPT1_ora_4622.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/app/oracle/product/11.2.0
System name:    SunOS
Node name:      zge-mktred-dpd1
Release:        5.10
Version:        Generic_142900-15
Machine:        sun4u
Instance name: BUSDPT1
Redo thread mounted by this instance: 1
Oracle process number: 43
Unix process pid: 4622, image: oracle@zge-mktred-dpd1 (TNS V1-V3)
 
 
*** 2012-09-13 18:47:04.505
*** SESSION ID:(298.353) 2012-09-13 18:47:04.505
*** CLIENT ID:() 2012-09-13 18:47:04.505
*** SERVICE NAME:(SYS$USERS) 2012-09-13 18:47:04.505
*** MODULE NAME:(SQL*Plus) 2012-09-13 18:47:04.505
*** ACTION NAME:() 2012-09-13 18:47:04.505
 
=====================
PARSING IN CURSOR #3 len=24 dep=0 uid=8518 oct=3 lid=8518 tim=49423238065693 hv=2343063137 ad='3926c9d30' sqlid='7h35uxf5uhmm1'
select sysdate from dual
END OF STMT
PARSE #3:c=0,e=1675,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1546270724,tim=49423238065691
EXEC #3:c=0,e=70,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1546270724,tim=49423238269464
FETCH #3:c=0,e=92,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1546270724,tim=49423238269701
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)'
FETCH #3:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1546270724,tim=49423238270733
BUSDPT1 /oracle/app/oracle/product/diag/rdbms/busdpt1/BUSDPT1/trace >

No comments: