Trace de outra sessão
Um DBA do trabalho me perguntou ontem como fazer isso. Como eu achei que já tinha aqui no Blog, pedi para ele procurar aqui, mas não tinha. Bom, agora tem.
Enter user-name: / as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SET PAGES 1000
SQL> SET LINES 210
SQL> SELECT S.USERNAME, P.SPID OS_PROCESS_ID, P.PID ORACLE_PROCESS_ID FROM V$SESSION S, V$PROCESS P WHERE S.PADDR = P.ADDR AND S.USERNAME = 'TESTE123';
USERNAME OS_PROCESS_I ORACLE_PROCESS_ID
------------------------------ ------------ -----------------
TESTE123 12062766 25
SQL> oradebug setospid 12062766
Oracle pid: 25, Unix process pid: 12062766, image: oracle@sslzuecom01 (TNS V1-V3)
SQL> oradebug tracefile_name
/oracle/admin/uecomprd/10g/udump/uecomprd_ora_12062766.trc
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug event 10046 trace name context forever, level 12;
Statement processed.
SQL> !tail -f /oracle/admin/uecomprd/10g/udump/uecomprd_ora_12062766.trc
*** 2010-06-23 19:30:20.418
*** ACTION NAME:() 2010-06-23 19:30:20.416
*** MODULE NAME:(SQL*Plus) 2010-06-23 19:30:20.416
*** SERVICE NAME:(SYS$USERS) 2010-06-23 19:30:20.416
*** SESSION ID:(654.3617) 2010-06-23 19:30:20.416
Received ORADEBUG command 'tracefile_name' from process Unix process pid: 5705880, image:
Received ORADEBUG command 'unlimit' from process Unix process pid: 5705880, image:
*** 2010-06-23 19:31:21.003
Received ORADEBUG command 'event 10046 trace name context forever, level 12' from process Unix process pid: 5705880, image:
*** 2010-06-23 19:32:16.546
WAIT #0: nam='SQL*Net message from client' ela= 165791477 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=13274672791619
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #2 len=198 dep=1 uid=0 oct=3 lid=0 tim=13274672792640 hv=4125641360 ad='beef74a8'
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
END OF STMT
PARSE #2:c=0,e=97,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=13274672792635
BINDS #2:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=1104dcfb8 bln=22 avl=03 flg=05
value=3797
Bind#1
oacdty=01 mxl=32(12) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=31 siz=32 off=0
kxsbbbfp=1104dcf80 bln=32 avl=12 flg=05
value="USER_OBJECTS"
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=1104dcf50 bln=24 avl=02 flg=05
value=1
EXEC #2:c=0,e=273,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=13274672793126
FETCH #2:c=0,e=35,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=13274672793191
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=44 us)'
STAT #2 id=2 cnt=0 pid=1 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=39 us)'
=====================
PARSING IN CURSOR #2 len=37 dep=1 uid=0 oct=3 lid=0 tim=13274672793619 hv=1398610540 ad='bee6d5e0'
select text from view$ where rowid=:1
END OF STMT
PARSE #2:c=0,e=47,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=13274672793617
BINDS #2:
kkscoacd
Bind#0
oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
kxsbbbfp=1104db828 bln=16 avl=16 flg=05
value=0000122C.0001.0001
EXEC #2:c=0,e=82,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=13274672793768
WAIT #2: nam='db file sequential read' ela= 10885 file#=1 block#=4652 blocks=1 obj#=-1 tim=13274672804839
FETCH #2:c=0,e=11140,p=1,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=13274672804928
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=62 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=1 pw=0 time=11126 us)'
=====================
PARSING IN CURSOR #1 len=44 dep=0 uid=3797 oct=1 lid=3797 tim=13274672812176 hv=2039720532 ad='a3767c60'
CREATE TABLE T AS SELECT * FROM USER_OBJECTS
END OF STMT
PARSE #1:c=0,e=20404,p=1,cr=5,cu=0,mis=1,r=0,dep=0,og=1,tim=13274672812172
BINDS #1:
EXEC #1:c=0,e=67,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=13274672812298