10046事件演示

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> select scn_to_timestamp(12121212) from dual;
select scn_to_timestamp(12121212) from dual
       *
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    2706735

SQL> select scn_to_timestamp(2706735) from dual;

SCN_TO_TIMESTAMP(2706735)
---------------------------------------------------------------------------
02-MAY-14 10.52.34.000000000 AM

SQL> alter session set events '10046 trace name context off';

Session altered.

SQL> select * from v$diag_info;

   INST_ID NAME                 VALUE
---------- -------------------- ----------------------------------------
         1 Diag Enabled         TRUE
         1 ADR Base             /u01/app/oracle
         1 ADR Home             /u01/app/oracle/diag/rdbms/test/test
         1 Diag Trace           /u01/app/oracle/diag/rdbms/test/test/tra
                                ce

         1 Diag Alert           /u01/app/oracle/diag/rdbms/test/test/ale
                                rt

         1 Diag Incident        /u01/app/oracle/diag/rdbms/test/test/inc
                                ident

         1 Diag Cdump           /u01/app/oracle/diag/rdbms/test/test/cdu
                                mp

         1 Health Monitor       /u01/app/oracle/diag/rdbms/test/test/hm
         1 Default Trace File   /u01/app/oracle/diag/rdbms/test/test/tra
                                ce/test_ora_23612.trc

         1 Active Problem Count 0
         1 Active Incident Coun 0
           t[root@node1

Trace file /u01/app/oracle/diag/rdbms/test/test/trace/test_ora_23612.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      node1
Release:        2.6.18-194.el5
Version:        #1 SMP Tue Mar 16 21:52:43 EDT 2010
Machine:        i686
Instance name: test
Redo thread mounted by this instance: 1
Oracle process number: 40
Unix process pid: 23612, image: (TNS V1-V3)

*** 2014-05-02 10:51:38.122
*** SESSION ID:(55.139) 2014-05-02 10:51:38.122
*** CLIENT ID:() 2014-05-02 10:51:38.122
*** SERVICE NAME:(SYS$USERS) 2014-05-02 10:51:38.122
*** MODULE NAME:( (TNS V1-V3)) 2014-05-02 10:51:38.122
*** ACTION NAME:() 2014-05-02 10:51:38.122
 
WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=73181 tim=1398999098121922

*** 2014-05-02 10:52:19.121
WAIT #3: nam='SQL*Net message from client' ela= 40999086 driver id=1650815232 #bytes=1 p3=0 obj#=73181 tim=13989991391212
29
CLOSE #3:c=0,e=22,dep=0,type=1,tim=1398999139121338
=====================
PARSING IN CURSOR #4 len=43 dep=0 uid=0 oct=3 lid=0 tim=1398999139526807 hv=525285836 ad='46f9a3b8' sqlid='5atvx6hgnyffc'
select scn_to_timestamp(12121212) from dual
END OF STMT
PARSE #4:c=3000,e=405438,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1388734953,tim=1398999139526804
EXEC #4:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=1398999139526891
WAIT #4: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=73181 tim=1398999139526911
FETCH #4:c=1000,e=96369,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=1398999139623299
STAT #4 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)'
=====================
PARSING IN CURSOR #1 len=168 dep=1 uid=57 oct=47 lid=57 tim=1398999139634213 hv=337957580 ad='49617328' sqlid='a6u3yjca29
nqc'
declare
  m_stmt  varchar2(512);
begin
   m_stmt:='delete from sdo_geor_ddl__table$$';
   EXECUTE IMMEDIATE m_stmt;
   EXCEPTION
     WHEN OTHERS THEN
       NULL;
end;
END OF STMT
PARSE #1:c=0,e=49,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=0,tim=1398999139634212
=====================
PARSING IN CURSOR #2 len=33 dep=2 uid=57 oct=7 lid=57 tim=1398999139634903 hv=1949913731 ad='4bb5fc64' sqlid='3972rvxu3kn
n3'
delete from sdo_geor_ddl__table$$
END OF STMT
PARSE #2:c=0,e=578,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,plh=3007952250,tim=1398999139634902
EXEC #2:c=1000,e=661,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=3007952250,tim=1398999139635601
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE  SDO_GEOR_DDL__TABLE$$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #2 id=2 cnt=0 pid=1 pos=1 obj=65908 op='TABLE ACCESS FULL SDO_GEOR_DDL__TABLE$$ (cr=0 pr=0 pw=0 time=0 us cost=2 siz
e=0 card=1)'
CLOSE #2:c=0,e=2,dep=2,type=3,tim=1398999139635658
EXEC #1:c=1000,e=1398,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=0,tim=1398999139635672
CLOSE #1:c=0,e=9,dep=1,type=1,tim=1398999139635700
XCTEND rlbk=0, rd_only=1, tim=1398999139635730
WAIT #4: nam='SQL*Net break/reset to client' ela= 13 driver id=1650815232 break?=1 p3=0 obj#=-1 tim=1398999139635781
WAIT #4: nam='SQL*Net break/reset to client' ela= 72 driver id=1650815232 break?=0 p3=0 obj#=-1 tim=1398999139635870

*** 2014-05-02 10:52:37.966
WAIT #4: nam='SQL*Net message from client' ela= 18316230 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1398999157966030
CLOSE #4:c=0,e=16,dep=0,type=0,tim=1398999157966107
=====================
PARSING IN CURSOR #2 len=210 dep=1 uid=0 oct=3 lid=0 tim=1398999157967678 hv=864012087 ad='525555c0' sqlid='96g93hntrzjtr
'
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval,
hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
END OF STMT
PARSE #2:c=1000,e=341,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=3,plh=0,tim=1398999157967322
BINDS #2:
 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=00ab2438  bln=22  avl=06  flg=05
  value=4294951038
 Bind#1
  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=00ab2414  bln=24  avl=02  flg=05
  value=60
EXEC #2:c=999,e=43806,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=3,plh=2239883476,tim=1398999158011604
FETCH #2:c=0,e=15,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=3,plh=2239883476,tim=1398999158011649
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=424 op='TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=2 pr=0 pw=0 time=0 us)'
STAT #2 id=2 cnt=0 pid=1 pos=1 obj=426 op='INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=0 us)'
CLOSE #2:c=0,e=2,dep=1,type=3,tim=1398999158011710
=====================
PARSING IN CURSOR #3 len=34 dep=0 uid=0 oct=3 lid=0 tim=1398999158012010 hv=269926743 ad='4bb67418' sqlid='adnu4f081dhar'
select current_scn from v$database
END OF STMT
PARSE #3:c=2999,e=45867,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=735420252,tim=1398999158012009
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=735420252,tim=1398999158012016
WAIT #3: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1398999158012016
WAIT #3: nam='Disk file operations I/O' ela= 0 FileOperation=2 fileno=0 filetype=1 obj#=-1 tim=1398999158012016
WAIT #3: nam='Disk file operations I/O' ela= 0 FileOperation=2 fileno=1 filetype=1 obj#=-1 tim=1398999158012016
WAIT #3: nam='control file sequential read' ela= 0 file#=0 block#=1 blocks=1 obj#=-1 tim=1398999158012017
WAIT #3: nam='control file sequential read' ela= 5 file#=0 block#=16 blocks=1 obj#=-1 tim=1398999158012061
WAIT #3: nam='control file sequential read' ela= 4 file#=0 block#=18 blocks=1 obj#=-1 tim=1398999158012081
WAIT #3: nam='control file sequential read' ela= 4 file#=0 block#=1 blocks=1 obj#=-1 tim=1398999158012181
WAIT #3: nam='control file sequential read' ela= 3 file#=0 block#=16 blocks=1 obj#=-1 tim=1398999158012196
WAIT #3: nam='control file sequential read' ela= 3 file#=0 block#=18 blocks=1 obj#=-1 tim=1398999158012208
WAIT #3: nam='control file sequential read' ela= 5 file#=0 block#=281 blocks=1 obj#=-1 tim=1398999158012221
FETCH #3:c=1000,e=228,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=735420252,tim=1398999158012244
WAIT #3: nam='SQL*Net message from client' ela= 110 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1398999158012378
FETCH #3:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=735420252,tim=1398999158012407
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=0 us cost=0 size=2300 card=100)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=0 op='FIXED TABLE FULL X$KCCDI (cr=0 pr=0 pw=0 time=0 us cost=0 size=23 card=1)'
STAT #3 id=3 cnt=1 pid=1 pos=2 obj=0 op='BUFFER SORT (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=100)'
STAT #3 id=4 cnt=1 pid=3 pos=1 obj=0 op='FIXED TABLE FULL X$KCCDI2 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=100)'
WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1398999158012474

*** 2014-05-02 10:52:57.736
WAIT #3: nam='SQL*Net message from client' ela= 19724485 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1398999177736968
CLOSE #3:c=0,e=16,dep=0,type=0,tim=1398999177737046
=====================
PARSING IN CURSOR #1 len=42 dep=0 uid=0 oct=3 lid=0 tim=1398999177738671 hv=2777423523 ad='46f9a540' sqlid='br8s8cykss7p3
'
select scn_to_timestamp(2706735) from dual
END OF STMT
PARSE #1:c=2000,e=1589,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1388734953,tim=1398999177738670
EXEC #1:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=1398999177738760
WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1398999177738783
FETCH #1:c=0,e=75,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=1398999177738876
STAT #1 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)'
WAIT #1: nam='SQL*Net message from client' ela= 189 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1398999177739161
FETCH #1:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1388734953,tim=1398999177739190
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1398999177739206

*** 2014-05-02 10:53:25.653
WAIT #1: nam='SQL*Net message from client' ela= 27914165 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1398999205653407
CLOSE #1:c=0,e=0,dep=0,type=0,tim=1398999205653437
=====================
PARSING IN CURSOR #4 len=55 dep=0 uid=0 oct=42 lid=0 tim=1398999205653516 hv=2217940283 ad='0' sqlid='06nvwn223659v'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #4:c=0,e=79,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1398999205653516
EXEC #4:c=999,e=154,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1398999205653703