{"id":594,"date":"2012-02-24T23:57:55","date_gmt":"2012-02-24T15:57:55","guid":{"rendered":"https:\/\/yangtingkun.net\/?p=594"},"modified":"2012-02-27T08:35:19","modified_gmt":"2012-02-27T00:35:19","slug":"ddl%e8%a7%a6%e5%8f%91%e5%99%a8%e8%ae%be%e7%bd%ae%e5%af%bc%e8%87%b4ddl%e6%97%a0%e6%b3%95%e6%89%a7%e8%a1%8c%ef%bc%88%e4%ba%8c%ef%bc%89","status":"publish","type":"post","link":"https:\/\/yangtingkun.net\/?p=594","title":{"rendered":"DDL\u89e6\u53d1\u5668\u8bbe\u7f6e\u5bfc\u81f4DDL\u65e0\u6cd5\u6267\u884c\uff08\u4e8c\uff09"},"content":{"rendered":"

\u516c\u53f8\u6d4b\u8bd5\u6570\u636e\u5e93\u53d1\u73b0\u6267\u884cDDL\u62a5\u9519\u3002
\n\u7ee7\u7eed\u63cf\u8ff0\u95ee\u9898\u7684\u8bca\u65ad\u548c\u89e3\u51b3\u3002
\nDDL\u89e6\u53d1\u5668\u8bbe\u7f6e\u5bfc\u81f4DDL\u65e0\u6cd5\u6267\u884c\uff08\u4e00\uff09:http:\/\/yangtingkun.itpub.net\/post\/468\/526410<\/a>
\n\u4ece\u5bf9\u8c61\u5b9a\u4e49\u662f\u627e\u4e0d\u5230\u95ee\u9898\u7684\u539f\u56e0\u7684\uff0c\u53ea\u80fd\u901a\u8fc710046\u8ddf\u8e2a\u4e00\u4e0b\uff1a
\nSQL> ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT FOREVER, LEVEL 12’;
\nSession altered.
\nSQL> CREATE TABLE T_CREATE (ID NUMBER);
\nCREATE TABLE T_CREATE (ID NUMBER)
\n*
\nERROR at line 1:
\nORA-00604: error occurred at recursive SQL level 1
\nORA-30511: invalid DDL operation in system triggers
\nORA-06512: at line 24
\nORA-06512: at line 24
\nSQL> ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT OFF’;
\nSession altered.
\n\u67e5\u8be2\u5f97\u5230\u7684TRACE\u6587\u4ef6\uff1a<\/p>\n

*** ACTION NAME:() 2012-01-31 12:15:24.811\r\n*** MODULE NAME:(sqlplus@hpserver2.enmotech.com (TNS V1-V3)) 2012-01-31 12:15:24.811\r\n*** SERVICE NAME:(SYS$USERS) 2012-01-31 12:15:24.811\r\n*** SESSION ID:(146.138) 2012-01-31 12:15:24.811\r\nSkipped error 604 during the execution of DBFW_CONSOLE_ACCESS.TRIGGER_LOGIN \r\n*** 2012-01-31 12:15:24.811\r\nksedmp: internal or fatal error\r\nORA-00604: error occurred at recursive SQL level 1\r\nORA-30511: invalid DDL operation in system triggers\r\nORA-06512: at line 3\r\n*** 2012-01-31 12:26:10.929\r\n*** ACTION NAME:() 2012-01-31 12:26:10.929\r\n*** MODULE NAME:(SQL*Plus) 2012-01-31 12:26:10.929\r\nA deadlock among DDL and parse locks is detected.\r\nThis deadlock is usually due to user errors in\r\nthe design of an application or from issuing a set\r\nof concurrent statements which can cause a deadlock.\r\nThis should not be reported to Oracle Support.\r\nThe following information may aid in finding\r\nthe errors which cause the deadlock:\r\nORA-04020: deadlock detected while trying to lock object EYGLE.BIN$trcEn8qthIjgQKjAEwAm+g==$0\r\n--------------------------------------------------------\r\n  object   waiting  waiting       blocking blocking\r\n  handle   session     lock mode   session     lock mode\r\n--------  -------- -------- ----  -------- -------- ----\r\n0xb88364d8  0xb8f713c8 0xb56ca598    X  0xb8f713c8 0xb56ca7c0    X\r\n--------------------------------------------------------\r\n---------- DUMP OF WAITING AND BLOCKING LOCKS ----------\r\n--------------------------------------------------------\r\n------------- WAITING LOCK -------------\r\n----------------------------------------\r\nSO: 0xb56ca598, type: 53, owner: 0xb73c2990, flag: INIT\/-\/-\/0x00\r\nLIBRARY OBJECT LOCK: lock=b56ca598 handle=b88364d8 request=X\r\ncall pin=(nil) session pin=(nil) hpc=0000 hlc=0000\r\nhtl=0xb56ca618[0xb56ca840,0xb53b1e18] htb=0xb53b1e18 ssga=0xb53b13b0\r\nuser=b8f713c8 session=b8f713c8 count=0 flags=[0000] savepoint=0x1494\r\nLIBRARY OBJECT HANDLE: handle=b88364d8 mtx=0xb8836608(0) cdp=0\r\nname=EYGLE.BIN$trcEn8qthIjgQKjAEwAm+g==$0 \r\nhash=41d7b6fc85abfd51d2baef487c92dc95 timestamp=01-17-2012 17:33:09\r\nnamespace=TABL flags=KGHP\/TIM\/FUL\/FUP\/SML\/[0e000000]\r\nkkkk-dddd-llll=0000-0701-0701 lock=X pin=X latch#=3 hpc=01d6 hlc=01d6\r\nlwt=0xb8836580[0xb56ca5c8,0xb56ca5c8] ltm=0xb8836590[0xb8836590,0xb8836590]\r\npwt=0xb8836548[0xb8836548,0xb8836548] ptm=0xb8836558[0xb8836558,0xb8836558]\r\nref=0xb88365b0[0xb88365b0,0xb88365b0] lnd=0xb88365c8[0xb88365c8,0xb88365c8]\r\n  LIBRARY OBJECT: object=b48a8d18\r\n  type=TABL flags=EXS\/LOC\/UPD\/PRG[0985] pflags=[0040] status=VALD load=0\r\n  DATA BLOCKS:\r\n  data#     heap  pointer    status pins change whr\r\n  ----- -------- -------- --------- ---- ------ ---\r\n      0 b8836418 b48a8e30 I\/P\/A\/-\/-    0 NONE   00 \r\n      8 b48a88a8 b03cdbf8 I\/P\/A\/-\/-    1 UPDATE 00 \r\n      9 b48a8978 b03cd428 I\/P\/A\/-\/-    1 NONE   00 \r\n     10 b48a8a00 b03cd040 I\/P\/A\/-\/-    1 UPDATE 00 \r\n------------- BLOCKING LOCK ------------\r\n----------------------------------------\r\nSO: 0xb56ca7c0, type: 53, owner: 0xb73c20f0, flag: INIT\/-\/-\/0x00\r\nLIBRARY OBJECT LOCK: lock=b56ca7c0 handle=b88364d8 mode=X\r\ncall pin=(nil) session pin=(nil) hpc=0000 hlc=0000\r\nhtl=0xb56ca840[0xb53b1e18,0xb56ca618] htb=0xb53b1e18 ssga=0xb53b13b0\r\nuser=b8f713c8 session=b8f82a10 count=2 flags=[0000] savepoint=0xd70\r\nLIBRARY OBJECT HANDLE: handle=b88364d8 mtx=0xb8836608(0) cdp=0\r\nname=EYGLE.BIN$trcEn8qthIjgQKjAEwAm+g==$0 \r\nhash=41d7b6fc85abfd51d2baef487c92dc95 timestamp=01-17-2012 17:33:09\r\nnamespace=TABL flags=KGHP\/TIM\/FUL\/FUP\/SML\/[0e000000]\r\nkkkk-dddd-llll=0000-0701-0701 lock=X pin=X latch#=3 hpc=01d6 hlc=01d6\r\nlwt=0xb8836580[0xb56ca5c8,0xb56ca5c8] ltm=0xb8836590[0xb8836590,0xb8836590]\r\npwt=0xb8836548[0xb8836548,0xb8836548] ptm=0xb8836558[0xb8836558,0xb8836558]\r\nref=0xb88365b0[0xb88365b0,0xb88365b0] lnd=0xb88365c8[0xb88365c8,0xb88365c8]\r\n  LIBRARY OBJECT: object=b48a8d18\r\n  type=TABL flags=EXS\/LOC\/UPD\/PRG[0985] pflags=[0040] status=VALD load=0\r\n  DATA BLOCKS:\r\n  data#     heap  pointer    status pins change whr\r\n  ----- -------- -------- --------- ---- ------ ---\r\n      0 b8836418 b48a8e30 I\/P\/A\/-\/-    0 NONE   00 \r\n      8 b48a88a8 b03cdbf8 I\/P\/A\/-\/-    1 UPDATE 00 \r\n      9 b48a8978 b03cd428 I\/P\/A\/-\/-    1 NONE   00 \r\n     10 b48a8a00 b03cd040 I\/P\/A\/-\/-    1 UPDATE 00 \r\n--------------------------------------------------------\r\nThis lock request was aborted.\r\n*** 2012-01-31 17:25:14.137\r\nA deadlock among DDL and parse locks is detected.\r\nThis deadlock is usually due to user errors in\r\nthe design of an application or from issuing a set\r\nof concurrent statements which can cause a deadlock.\r\nThis should not be reported to Oracle Support.\r\nThe following information may aid in finding\r\nthe errors which cause the deadlock:\r\nORA-04020: deadlock detected while trying to lock object EYGLE.BIN$trcI7ykLAu7gQKjAEwAnkA==$0\r\n--------------------------------------------------------\r\n  object   waiting  waiting       blocking blocking\r\n  handle   session     lock mode   session     lock mode\r\n--------  -------- -------- ----  -------- -------- ----\r\n0xb209bfc0  0xb8f713c8 0xb56c3aa0    X  0xb8f713c8 0xb56756f0    X\r\n--------------------------------------------------------\r\n---------- DUMP OF WAITING AND BLOCKING LOCKS ----------\r\n--------------------------------------------------------\r\n------------- WAITING LOCK -------------\r\n----------------------------------------\r\nSO: 0xb56c3aa0, type: 53, owner: 0xb77d5078, flag: INIT\/-\/-\/0x00\r\nLIBRARY OBJECT LOCK: lock=b56c3aa0 handle=b209bfc0 request=X\r\ncall pin=(nil) session pin=(nil) hpc=0000 hlc=0000\r\nhtl=0xb56c3b20[0xb5675770,0xb53b1b08] htb=0xb53b1b08 ssga=0xb53b13b0\r\nuser=b8f713c8 session=b8f713c8 count=0 flags=[0000] savepoint=0x386e\r\nLIBRARY OBJECT HANDLE: handle=b209bfc0 mtx=0xb209c0f0(0) cdp=0\r\nname=EYGLE.BIN$trcI7ykLAu7gQKjAEwAnkA==$0 \r\nhash=f2ab522fb35d982d1fbc28237e665464 timestamp=01-17-2012 17:34:17\r\nnamespace=TABL flags=KGHP\/TIM\/FUP\/SML\/[0a000000]\r\nkkkk-dddd-llll=0000-0701-0701 lock=X pin=X latch#=2 hpc=0038 hlc=0038\r\nlwt=0xb209c068[0xb56c3ad0,0xb56c3ad0] ltm=0xb209c078[0xb209c078,0xb209c078]\r\npwt=0xb209c030[0xb209c030,0xb209c030] ptm=0xb209c040[0xb209c040,0xb209c040]\r\nref=0xb209c098[0xb209c098,0xb209c098] lnd=0xb209c0b0[0xb209c0b0,0xb209c0b0]\r\n  LIBRARY OBJECT: object=b0c49fd8\r\n  type=TABL flags=EXS\/LOC\/UPD\/PRG[0985] pflags=[0040] status=VALD load=0\r\n  DATA BLOCKS:\r\n  data#     heap  pointer    status pins change whr\r\n  ----- -------- -------- --------- ---- ------ ---\r\n      0 b209bf00 b0c4a0f0 I\/P\/A\/-\/-    0 NONE   00 \r\n      8 b0c49b68 b0201480 I\/P\/A\/-\/-    1 UPDATE 00 \r\n      9 b0c49c38 b0200cb0 I\/P\/A\/-\/-    1 NONE   00 \r\n     10 b0c49cc0 b01af398 I\/P\/A\/-\/-    1 UPDATE 00 \r\n------------- BLOCKING LOCK ------------\r\n----------------------------------------\r\nSO: 0xb56756f0, type: 53, owner: 0xb77d47d8, flag: INIT\/-\/-\/0x00\r\nLIBRARY OBJECT LOCK: lock=b56756f0 handle=b209bfc0 mode=X\r\ncall pin=(nil) session pin=(nil) hpc=0000 hlc=0000\r\nhtl=0xb5675770[0xb53b1b08,0xb56c3b20] htb=0xb53b1b08 ssga=0xb53b13b0\r\nuser=b8f713c8 session=b8f6fe60 count=2 flags=[0000] savepoint=0x35eb\r\nLIBRARY OBJECT HANDLE: handle=b209bfc0 mtx=0xb209c0f0(0) cdp=0\r\nname=EYGLE.BIN$trcI7ykLAu7gQKjAEwAnkA==$0 \r\nhash=f2ab522fb35d982d1fbc28237e665464 timestamp=01-17-2012 17:34:17\r\nnamespace=TABL flags=KGHP\/TIM\/FUP\/SML\/[0a000000]\r\nkkkk-dddd-llll=0000-0701-0701 lock=X pin=X latch#=2 hpc=0038 hlc=0038\r\nlwt=0xb209c068[0xb56c3ad0,0xb56c3ad0] ltm=0xb209c078[0xb209c078,0xb209c078]\r\npwt=0xb209c030[0xb209c030,0xb209c030] ptm=0xb209c040[0xb209c040,0xb209c040]\r\nref=0xb209c098[0xb209c098,0xb209c098] lnd=0xb209c0b0[0xb209c0b0,0xb209c0b0]\r\n  LIBRARY OBJECT: object=b0c49fd8\r\n  type=TABL flags=EXS\/LOC\/UPD\/PRG[0985] pflags=[0040] status=VALD load=0\r\n  DATA BLOCKS:\r\n  data#     heap  pointer    status pins change whr\r\n  ----- -------- -------- --------- ---- ------ ---\r\n      0 b209bf00 b0c4a0f0 I\/P\/A\/-\/-    0 NONE   00 \r\n      8 b0c49b68 b0201480 I\/P\/A\/-\/-    1 UPDATE 00 \r\n      9 b0c49c38 b0200cb0 I\/P\/A\/-\/-    1 NONE   00 \r\n     10 b0c49cc0 b01af398 I\/P\/A\/-\/-    1 UPDATE 00 \r\n--------------------------------------------------------\r\nThis lock request was aborted.\r\n*** 2012-01-31 17:39:16.132\r\nWAIT #2: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=37 tim=1296877691535577\r\n*** 2012-01-31 17:40:12.165\r\nWAIT #2: nam='SQL*Net message from client' ela= 54719914 driver id=1650815232 #bytes=1 p3=0 obj#=37 tim=1296877746255669\r\nXCTEND rlbk=0, rd_only=1\r\n=====================\r\nPARSING IN CURSOR #10 len=33 dep=0 uid=60 oct=1 lid=60 tim=1296877746256995 hv=4051323229 ad='b2088510'\r\nCREATE TABLE T_CREATE (ID NUMBER)\r\nEND OF STMT\r\nPARSE #10:c=0,e=885,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1296877746256954\r\nBINDS #10:\r\n=====================\r\n.\r\n.\r\n.\r\n=====================\r\nPARSING IN CURSOR #13 len=753 dep=1 uid=58 oct=47 lid=58 tim=1296877746275168 hv=2905514213 ad='b8852970'\r\ndeclare\r\n  li ora_name_list_t;\r\n  n integer;\r\n  cSQL clob;\r\nbegin\r\n  if dbfw_console_access.is_local then\r\n    if ora_dict_obj_type = 'TABLE' and\r\n       ora_dict_obj_owner = 'DBFW_CONSOLE_ACCESS' and\r\n       ora_dict_obj_name = 'EVENT' then\r\n      null;\r\n    else\r\n      n := ora_sql_txt(li);\r\n      for i in 1 .. n loop\r\n        cSQL := cSQL || li(i);\r\n      end loop;\r\n      if cSQL is not null then\r\n        insert into dbfw_console_access.event(id,username,sessionid,event,text)\r\n          select dbfw_console_access.event_seq.nextval,\r\n                 sys_context('USERENV','SESSION_USER'),\r\n                 sys_context('USERENV','SESSIONID'),\r\n                 'DDL',\r\n                 cSQL\r\n            from dual;\r\n        end if;\r\n    end if;\r\n  end if;\r\nend;\r\nEND OF STMT\r\nPARSE #13:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1296877746275166\r\nBINDS #13:\r\n=====================\r\nPARSING IN CURSOR #5 len=213 dep=2 uid=58 oct=2 lid=58 tim=1296877746275611 hv=1466247469 ad='b88468c0'\r\nINSERT INTO DBFW_CONSOLE_ACCESS.EVENT(ID,USERNAME,SESSIONID,EVENT,TEXT) SELECT DBFW_CONSOLE_ACCESS.EVENT_SEQ.NEXTVAL, SYS_CONTEXT('USERENV','SESSION_USER'), \r\nSYS_CONTEXT('USERENV','SESSIONID'), 'DDL', :B1 FROM DUAL\r\nEND OF STMT\r\nPARSE #5:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=1296877746275608\r\nBINDS #5:\r\nkkscoacd\r\n Bind#0\r\n  oacdty=112 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00\r\n  oacflg=01 fl2=206001 frm=01 csi=873 siz=4000 off=0\r\n  kxsbbbfp=7fa2b3cf99c0  bln=4000  avl=40  flg=05\r\n  value=\r\nDump of memory from 0x00007FA2B3CF99C0 to 0x00007FA2B3CF99E8\r\n7FA2B3CF99C0 01002600 03800802 00000200 0092008A  [.&..............]\r\n7FA2B3CF99D0 08000000 17006903 01000000 00000000  [.....i..........]\r\n7FA2B3CF99E0 F9D69D36 5422DE81                    [6.....\"T]        \r\n=====================\r\n.\r\n.\r\n.\r\n=====================\r\nPARSING IN CURSOR #4 len=57 dep=3 uid=0 oct=12 lid=0 tim=1296877746278076 hv=0 ad='b40eead8'\r\ndrop table \"EYGLE\".\"BIN$trcI7ykLAu7gQKjAEwAnkA==$0\" purge\r\nEND OF STMT\r\nPARSE #4:c=0,e=158,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=4,tim=1296877746278074\r\nBINDS #4:\r\n=====================\r\n.\r\n.\r\n.\r\n=====================\r\nPARSING IN CURSOR #7 len=753 dep=4 uid=58 oct=47 lid=58 tim=1296877746356839 hv=2905514213 ad='b8852970'\r\ndeclare\r\n  li ora_name_list_t;\r\n  n integer;\r\n  cSQL clob;\r\nbegin\r\n  if dbfw_console_access.is_local then\r\n    if ora_dict_obj_type = 'TABLE' and\r\n       ora_dict_obj_owner = 'DBFW_CONSOLE_ACCESS' and\r\n       ora_dict_obj_name = 'EVENT' then\r\n      null;\r\n    else\r\n      n := ora_sql_txt(li);\r\n      for i in 1 .. n loop\r\n        cSQL := cSQL || li(i);\r\n      end loop;\r\n      if cSQL is not null then\r\n        insert into dbfw_console_access.event(id,username,sessionid,event,text)\r\n          select dbfw_console_access.event_seq.nextval,\r\n                 sys_context('USERENV','SESSION_USER'),\r\n                 sys_context('USERENV','SESSIONID'),\r\n                 'DDL',\r\n                 cSQL\r\n            from dual;\r\n        end if;\r\n    end if;\r\n  end if;\r\nend;\r\nEND OF STMT\r\nPARSE #7:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=4,og=1,tim=1296877746356836\r\nBINDS #7:\r\n=====================\r\nPARSING IN CURSOR #14 len=213 dep=5 uid=58 oct=2 lid=58 tim=1296877746357237 hv=1466247469 ad='b88468c0'\r\nINSERT INTO DBFW_CONSOLE_ACCESS.EVENT(ID,USERNAME,SESSIONID,EVENT,TEXT) SELECT DBFW_CONSOLE_ACCESS.EVENT_SEQ.NEXTVAL, SYS_CONTEXT('USERENV','SESSION_USER'), \r\nSYS_CONTEXT('USERENV','SESSIONID'), 'DDL', :B1 FROM DUAL\r\nEND OF STMT\r\nPARSE #14:c=0,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=5,og=1,tim=1296877746357234\r\nBINDS #14:\r\nkkscoacd\r\n Bind#0\r\n  oacdty=112 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00\r\n  oacflg=01 fl2=206001 frm=01 csi=873 siz=4000 off=0\r\n  kxsbbbfp=7fa2b3d2d680  bln=4000  avl=40  flg=05\r\n  value=\r\nDump of memory from 0x00007FA2B3D2D680 to 0x00007FA2B3D2D6A8\r\n7FA2B3D2D680 01002600 03800802 00000200 0092008A  [.&..............]\r\n7FA2B3D2D690 0A000000 31006903 01000000 00000000  [.....i.1........]\r\n7FA2B3D2D6A0 30937991 2DB5F5E9                    [.y.0...-]        \r\n=====================\r\n.\r\n.\r\n.\r\n=====================\r\nPARSING IN CURSOR #11 len=51 dep=7 uid=0 oct=10 lid=0 tim=1296877746421849 hv=0 ad='b40e3b50'\r\ndrop index \"EYGLE\".\"BIN$trcI7ykNAu7gQKjAEwAnkA==$0\"\r\nEND OF STMT\r\nPARSE #11:c=5000,e=19651,p=1,cr=66,cu=0,mis=1,r=0,dep=7,og=4,tim=1296877746421846\r\nBINDS #11:\r\nEXEC #1:c=10999,e=55067,p=2,cr=154,cu=18,mis=0,r=0,dep=6,og=4,tim=1296877746421937\r\nERROR #1:err=30511 tim=793245673\r\nSTAT #12 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=12 pr=0 pw=0 time=184 us)'\r\nSTAT #12 id=2 cnt=1 pid=1 pos=1 obj=32 op='TABLE ACCESS BY INDEX ROWID CCOL$ (cr=4 pr=0 pw=0 time=33 us)'\r\nSTAT #12 id=3 cnt=1 pid=2 pos=1 obj=54 op='INDEX RANGE SCAN I_CCOL1 (cr=3 pr=0 pw=0 time=19 us)'\r\nSTAT #12 id=4 cnt=1 pid=1 pos=2 obj=21 op='TABLE ACCESS BY INDEX ROWID COL$ (cr=8 pr=0 pw=0 time=149 us)'\r\nSTAT #12 id=5 cnt=1 pid=4 pos=1 obj=47 op='INDEX UNIQUE SCAN I_COL3 (cr=4 pr=0 pw=0 time=79 us)'\r\nSTAT #2 id=1 cnt=2 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=3 pr=0 pw=0 time=67 us)'\r\nSTAT #2 id=2 cnt=3 pid=1 pos=1 obj=702 op='TABLE ACCESS FULL RECYCLEBIN$ (cr=3 pr=0 pw=0 time=40 us)'\r\nEXEC #14:c=17998,e=65066,p=2,cr=373,cu=57,mis=0,r=0,dep=5,og=1,tim=1296877746422333\r\nERROR #14:err=604 tim=793245673\r\nEXEC #7:c=17998,e=65535,p=2,cr=374,cu=82,mis=0,r=0,dep=4,og=1,tim=1296877746422463\r\nERROR #7:err=30511 tim=793245673\r\nXCTEND rlbk=1, rd_only=1\r\nSTAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=3 pr=0 pw=0 time=75 us)'\r\nSTAT #3 id=2 cnt=3 pid=1 pos=1 obj=702 op='TABLE ACCESS FULL RECYCLEBIN$ (cr=3 pr=0 pw=0 time=48 us)'\r\nEXEC #5:c=73990,e=147097,p=5,cr=882,cu=104,mis=0,r=0,dep=2,og=1,tim=1296877746422739\r\nERROR #5:err=604 tim=793245673\r\nEXEC #13:c=73990,e=147555,p=5,cr=883,cu=130,mis=0,r=0,dep=1,og=1,tim=1296877746422827\r\nERROR #13:err=30511 tim=793245673\r\nWAIT #10: nam='log file sync' ela= 76283 buffer#=2642 p2=4987314 p3=0 obj#=-1 tim=1296877746499472\r\nWAIT #10: nam='SQL*Net break\/reset to client' ela= 3 driver id=1650815232 break?=1 p3=0 obj#=-1 tim=1296877746499549\r\nWAIT #10: nam='SQL*Net break\/reset to client' ela= 92 driver id=1650815232 break?=0 p3=0 obj#=-1 tim=1296877746499674\r\nWAIT #10: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1296877746499688\r\n*** 2012-01-31 17:40:22.304\r\nWAIT #10: nam='SQL*Net message from client' ela= 9656896 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1296877756156629\r\n=====================\r\nPARSING IN CURSOR #19 len=55 dep=0 uid=60 oct=42 lid=60 tim=1296877756157111 hv=524428051 ad='0'\r\nALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'\r\nEND OF STMT\r\nPARSE #19:c=0,e=198,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1296877756157104\r\nEXEC #19:c=0,e=83,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1296877756157288<\/pre>\n
\u4e0d\u96be\u770b\u51fa\uff0c\u5bfc\u81f4\u95ee\u9898\u4ea7\u751f\u7684\u539f\u56e0\u662f\u7531\u4e8e\u5728\u6267\u884c\u4e86CREATE TABLE\u8bed\u53e5\u540e\uff0c\u89e6\u53d1\u5668\u5f15\u53d1\u4e86\u5220\u9664\u56de\u6536\u7ad9\u4e2d\u7684\u7d22\u5f15\u548c\u8868\u7684DDL\uff0c\u800c\u8fd9\u4e2aDDL\u53c8\u4f1a\u518d\u6b21\u5f15\u53d1\u89e6\u53d1\u5668\u7684\u89e6\u53d1\uff0c\u4ece\u800c\u5728\u4e00\u4e2a\u4f1a\u8bdd\u4e2d\u5f15\u5165\u4e86\u6b7b\u9501\u3002
\n\u4e0d\u8fc7\u4e3a\u4ec0\u4e48\u89e6\u53d1\u5668\u4f1a\u5bfc\u81f4\u56de\u6536\u7ad9\u4e2d\u7684\u7d22\u5f15\u548c\u8868\u7684DROP\u52a8\u4f5c\u5462\uff0c\u67e5\u8be2\u540e\u53d1\u73b0\uff0c\u539f\u6765\u89e6\u53d1\u5668\u7684\u89e6\u53d1\u52a8\u4f5c\u662f\u4e00\u4e2aINSERT\u8bed\u53e5\uff0c\u800c\u5f53\u524d\u8868\u4e2d\u6570\u636e\u5df2\u6ee1\uff0c\u9700\u8981\u6269\u5c55\u65b0\u7684\u7a7a\u95f4\uff0c\u800c\u6070\u597d\u8fd9\u4e2a\u5bf9\u8c61\u6240\u5728\u7684\u8868\u7a7a\u95f4\u4e5f\u6ca1\u6709\u5269\u4f59\u7a7a\u95f4\u4e86\uff0c\u6240\u4ee5Oracle\u4e0d\u5f97\u4ee5\u8981\u6e05\u9664RECYCLEBIN\u4e2d\u7684\u5bf9\u8c61\u6765\u91ca\u653e\u65b0\u7684\u7a7a\u95f4\uff1a<\/p>\n
SQL> select table_name, tablespace_name from dba_tables \r\n2 WHERE TABLE_NAME = 'EVENT'\r\n3 AND OWNER = 'DBFW_CONSOLE_ACCESS';\r\nTABLE_NAME                     TABLESPACE_NAME\r\n------------------------------ ------------------------------\r\nEVENT                          USERS\r\nSQL> SELECT SUM(BYTES)\/1024\/1024 FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = 'USERS';\r\nSUM(BYTES)\/1024\/1024\r\n--------------------\r\n<\/pre>\n
\u627e\u5230\u95ee\u9898\u7684\u539f\u56e0\u89e3\u51b3\u8d77\u6765\u5c31\u5f88\u7b80\u5355\u4e86\uff0c\u4e0d\u8fc7\u7531\u4e8eDDL\u89e6\u53d1\u5668\u4f1a\u5bfc\u81f4INSERT\uff0c\u56e0\u6b64\u9664\u4e86PURGE\u4e4b\u5916\uff0c\u6240\u6709\u5176\u4ed6\u7684\u5c1d\u8bd5\u90fd\u4f1a\u62a5\u9519\uff1a<\/p>\n
SQL> conn \/ as sysdba\r\nConnected.\r\nSQL> purge table eygle.t_pwd;\r\nTable purged.\r\nSQL> select * from dba_recyclebin;\r\nno rows selected\r\nSQL> CREATE TABLE T_CREATE (ID NUMBER);\r\nTable created.<\/pre>\n
\u597d\u5728PURGE\u8bed\u53e5\u6ca1\u6709\u5bfc\u81f4\u89e6\u53d1\u5668\u7684\u89e6\u53d1\uff0c\u4ece\u800c\u907f\u514d\u4e86\u9519\u8bef\u7684\u4ea7\u751f\uff0c\u5982\u679cPURGE\u540c\u6837\u5bfc\u81f4\u89e6\u53d1\u5668\u8fd0\u884c\uff0c\u6216\u8005RECYCLEBIN\u4e2d\u6ca1\u6709\u5bf9\u8c61\u53ef\u4ee5\u6e05\u9664\uff0c\u90a3\u4e48\u5f53\u524d\u7684\u95ee\u9898\u53ef\u80fd\u5c31\u5f88\u96be\u7528\u5e38\u89c4\u624b\u6bb5\u89e3\u51b3\u4e86\u3002
\n\u8fd9\u4e5f\u8bf4\u660e\u4e00\u4e2a\u95ee\u9898\uff0c\u5728DDL\u89e6\u53d1\u5668\u4e2d\u6267\u884c\u7684\u64cd\u4f5c\u4e00\u5b9a\u8981\u8c28\u614e\uff0c\u5426\u5219\u5f88\u5bb9\u6613\u5bfc\u81f4\u7cfb\u7edf\u7684\u6545\u969c\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
\u516c\u53f8\u6d4b\u8bd5\u6570\u636e\u5e93\u53d1\u73b0\u6267\u884cDDL\u62a5\u9519\u3002 \u7ee7\u7eed\u63cf\u8ff0\u95ee\u9898\u7684\u8bca\u65ad\u548c\u89e3\u51b3\u3002 DDL\u89e6\u53d1\u5668\u8bbe\u7f6e\u5bfc\u81f4DDL\u65e0\u6cd5\u6267\u884c\uff08\u4e00\uff09:http:\/\/yangtingkun.itpub.net\/post\/468\/526410 \u4ece\u5bf9\u8c61\u5b9a\u4e49\u662f\u627e\u4e0d\u5230\u95ee\u9898\u7684\u539f\u56e0\u7684\uff0c\u53ea\u80fd\u901a\u8fc710046\u8ddf\u8e2a\u4e00\u4e0b\uff1a SQL> ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT FOREVER, LEVEL 12’; Session altered. SQL> CREATE TABLE T_CREATE (ID NUMBER); CREATE TABLE T_CREATE (ID NUMBER) * ERROR at line 1: ORA-00604: error occurred at recursive SQL … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3],"tags":[482,481,484,483],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2yiY3-9A","_links":{"self":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/594"}],"collection":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=594"}],"version-history":[{"count":4,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/594\/revisions"}],"predecessor-version":[{"id":598,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/594\/revisions\/598"}],"wp:attachment":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=594"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=594"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=594"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}