{"id":1168,"date":"2013-12-19T18:38:12","date_gmt":"2013-12-19T10:38:12","guid":{"rendered":"http:\/\/www.yallonking.com\/?p=1168"},"modified":"2013-12-19T18:40:32","modified_gmt":"2013-12-19T10:40:32","slug":"%e5%94%af%e4%b8%80%e6%80%a7%e7%bb%84%e5%90%88%e7%b4%a2%e5%bc%95%e5%bc%95%e8%b5%b7%e7%9a%84%e6%8f%92%e5%85%a5%e7%9a%84sql-hang%e4%bd%8f%e7%9a%84%e5%88%86%e6%9e%90","status":"publish","type":"post","link":"http:\/\/www.yallonking.com\/?p=1168","title":{"rendered":"\u552f\u4e00\u6027\u7ec4\u5408\u7d22\u5f15\u5f15\u8d77\u7684\u63d2\u5165\u7684sql hang\u4f4f\u7684\u5206\u6790"},"content":{"rendered":"<p>\u552f\u4e00\u6027\u7ec4\u5408\u7d22\u5f15\u5f15\u8d77\u7684\u63d2\u5165\u7684sql hang\u4f4f\u7684\u5206\u6790<br \/>\n\u95ee\u9898\u5206\u6790\uff1a\u62e5\u6709\u76f8\u540c\u7ec4\u5408\u7d22\u5f15\u5217\u952e\u503c\u7684sql\uff0c\u5728\u6279\u91cf\u64cd\u4f5c\u65f6\u53ef\u80fd\u4f1a\u5bfc\u81f4undo\u4e89\u7528\uff0c\u81f4\u4f7f\u4f1a\u8bdd\u6302\u8d77\u3002<\/p>\n<p>=============\u4ee5\u4e0b\u4e3a\u5206\u6790\u8fc7\u7a0b=============<br \/>\n\u8868TBLCERTREQ\u6709\u4ee5\u4e0b\u6709\u5173\u7684\u552f\u4e00\u6027\u7d22\u5f15<br \/>\n\u7b2c\u4e00\u4e2a\uff1a\u4e3b\u952e\u4ea7\u751f\u7684\u552f\u4e00\u6027\u7d22\u5f15<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nSQL&gt; select * from dba_ind_columns where table_owner=&#039;CAUSER&#039; and table_name=&#039;TBLCERTREQ&#039; and index_name=&#039;SYS_C001605&#039;;\r\n\r\nINDEX_OWNE INDEX_NAME                     TABLE_OWNE TABLE_NAME COLUMN_NAME     COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC\r\n---------- ------------------------------ ---------- ---------- --------------- --------------- ------------- ----------- ----\r\nCAUSER     SYS_C001605                    CAUSER     TBLCERTREQ REF                           1            32          32 ASC\r\n<\/pre>\n<p>\u7b2c\u4e8c\u4e2a\uff1a\u7ec4\u5408\u952e\u503c\u4ea7\u751f\u7684\u552f\u4e00\u6027\u7d22\u5f15<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nSQL&gt; select * from dba_ind_columns where table_owner=&#039;CAUSER&#039; and table_name=&#039;TBLCERTREQ&#039; and index_name=&#039;TBLCERTREQ_INDEX02&#039;;\r\n\r\nINDEX_OWNER          INDEX_NAME                     TABLE_OWNE TABLE_NAME COLUMN_NAME          COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC\r\n-------------------- ------------------------------ ---------- ---------- -------------------- --------------- ------------- ----------- ----\r\nCAUSER               TBLCERTREQ_INDEX02             CAUSER     TBLCERTREQ DN                                 1           512         512 ASC\r\nCAUSER               TBLCERTREQ_INDEX02             CAUSER     TBLCERTREQ CERTTMLID                          2            22           0 ASC\r\nCAUSER               TBLCERTREQ_INDEX02             CAUSER     TBLCERTREQ USERSTATUS                         3            22           0 ASC\r\nCAUSER               TBLCERTREQ_INDEX02             CAUSER     TBLCERTREQ STATUSEX                           4            32          32 ASC\r\n<\/pre>\n<p>\u521b\u5efa\u65f6\u95f4<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nSQL&gt; select OWNER,OBJECT_NAME,CREATED from dba_objects where OBJECT_NAME=&#039;SYS_C001605&#039;;\r\n\r\nOWNER                          OBJECT_NAME          CREATED\r\n------------------------------ -------------------- -------------------\r\nCAUSER                         SYS_C001605          2009\/08\/01 01:50:45\r\n\r\nSQL&gt; select OWNER,OBJECT_NAME,CREATED from dba_objects where OBJECT_NAME=&#039;TBLCERTREQ_INDEX02&#039;;\r\n\r\nOWNER                          OBJECT_NAME          CREATED\r\n------------------------------ -------------------- -------------------\r\nCAUSER                         TBLCERTREQ_INDEX02   2009\/08\/01 01:49:30\r\n<\/pre>\n<p>\u7b2c\u4e00\u4e2a\u4f1a\u8bdd(session id:29)<br \/>\n\u63d2\u5165\u4e00\u6761\u8bb0\u5f55\u4f46\u8fdf\u8fdf\u4e0d\u63d0\u4ea4<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nSQL&gt; select sid from v$mystat where rownum&lt;2;\r\n\r\n       SID\r\n----------\r\n        29\r\n\r\nSQL&gt; insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)\r\n  2  values(&#039;08f03e7a3793acd295e1366c41dc5caf&#039;,&#039;AnszcgexrSoXfzgiZ\/0cj9h\/mtUZrvfCYM9GQnT8OWTpnAEqUn\/U6Uq4RHJSYYbj&gt;&#039;,\r\n  3  &#039;CN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA Operation CA2,C=CN&#039;,&#039;2013.11.22-18:38:00&#039;,&#039;2018.11.22-18:38:00&#039;,\r\n  4  113,0,3,&#039;2013.12.20-14:53:29&#039;,&#039;4LHHItaSMfB1q5HS8oX1KA==&gt;&#039;,&#039;0&#039;);\r\n\r\n1 row created.\r\n\r\nSQL&gt; insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)\r\n  2  values(&#039;08f03e7a3793acd295e1366c41dc5ccf&#039;,&#039;AnszcgexrSoXfzgiZ\/0cj9h\/mtUZrvfCYM9GQnT8OWTpnAEqUn\/U6Uq4RHJSYYbj&gt;&#039;,\r\n  3  &#039;CN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA Operation CA2,C=CN&#039;,&#039;2013.11.22-18:38:00&#039;,&#039;2018.11.22-18:38:00&#039;,\r\n  4  113,0,3,&#039;2013.12.20-14:53:29&#039;,&#039;4LHHItaSMfB1q5HS8oX1KA==&gt;&#039;,&#039;0&#039;);\r\ninsert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)\r\n*\r\nERROR at line 1:\r\nORA-00001: unique constraint (YALLONKING.TBLCERTREQ_INDEX02) violated\t\t&lt;-\u540c\u4e00\u4f1a\u8bdd\u4e2d\u7ee7\u7eed\u63d2\u5165\u7ec4\u5408\u7d22\u5f15\u503c\u76f8\u540c\u65f6\u62a5\u8fdd\u53cd\u552f\u4e00\u6027\u7d22\u5f15\u7684\u9519\u8bef\r\n\r\n<\/pre>\n<p>\u7b2c\u4e8c\u4e2a\u4f1a\u8bdd(session id:33)<br \/>\n\u6b63\u5e38\u64cd\u4f5c<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nSQL&gt; select sid from v$mystat where rownum&lt;2;\r\n\r\n       SID\r\n----------\r\n        33\r\n\t\t\r\nSQL&gt; insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)\r\n  2  values(&#039;08f03e7a3793acd295e1366c41dc5cag&#039;,&#039;AnszcgexrSoXfzgiZ\/0cj9h\/mtUZrvfCYM9GQnT8OWTpnAEqUn\/U6Uq4RHJSYYbj&gt;&#039;,\r\n  3  &#039;CN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA Operation CA2,C=CN&#039;,&#039;2013.11.22-18:38:00&#039;,&#039;2018.11.22-18:38:00&#039;,\r\n  4  113,0,3,&#039;2013.12.20-14:53:29&#039;,&#039;4LHHItaSMfB1q5HS8oX1KA==&gt;&#039;,&#039;0&#039;);\r\n^Cinsert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)\r\n*\r\nERROR at line 1:\r\nORA-01013: user requested cancel of current operation\t\t\t&lt;-\u6b64\u5904\u53d1\u73b0\u63d2\u5165hang\u4f4f\u4e86\uff0c\u4eba\u4e3a\u53d6\u6d88\r\n\r\n<\/pre>\n<p>\u6ce8\uff1a\u8fd9\u6b21\u63d2\u5165\u7684\u8bb0\u5f55\u4e2d\uff0c(DN,CERTTMLID,USERSTATUS,STATUSEX)\u7ec4\u548c\u503c\u548c\u7b2c\u4e00\u4e2a\u4f1a\u8bdd\u63d2\u5165\u7684\u4e00\u81f4\uff0c\u4e5f\u5c31\u662f\u5728\u8fdd\u53cd\u552f\u4e00\u6027\u7ec4\u5408\u7d22\u5f15\u7684\u60c5\u51b5\u4e0b\u63d2\u5165\u3002<br \/>\n\u7531\u4e8e\u7b2c\u4e00\u4e2a\u4f1a\u8bdd\u6ca1\u6709\u63d0\u4ea4\uff0c\u6839\u636e\u4e8b\u52a1ACID\u7279\u6027\u4e2d\u7684\u9694\u79bb\u6027\u4f7f\u5f97\u4f1a\u8bdd\u4e8c\u65e0\u6cd5\u770b\u5230\u4f1a\u8bdd\u4e00\u7684\u6570\u636e\uff0c\u6240\u4ee5\u6570\u636e\u5e93\u4e5f\u5c31\u4e0d\u4f1a\u62a5\u8fdd\u53cd\u552f\u4e00\u6027\u7684\u76f8\u5173\u9519\u8bef\u4ee5\u81f3\u4e8e\u4f1a\u8bdd\u4e8c\u4e00\u76f4\u7b49\u5f85\u4f1a\u8bdd\u4e00\u63d0\u4ea4\u3002\u5982\u679c\u4f1a\u8bdd\u4e00\u63d0\u4ea4\u540e\uff0c\u5219\u4f1a\u8bdd\u4e8c\u4f1a\u62a5\u8fdd\u53cd\u552f\u4e00\u6027\u7684\u76f8\u5173\u9519\u8bef\u3002<br \/>\n\u5982\u4e0b\uff1a<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nSQL&gt; insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)\r\n  2  values(&#039;08f03e7a3793acd295e1366c41dc5cag&#039;,&#039;AnszcgexrSoXfzgiZ\/0cj9h\/mtUZrvfCYM9GQnT8OWTpnAEqUn\/U6Uq4RHJSYYbj&gt;&#039;,\r\n  3  &#039;CN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA Operation CA2,C=CN&#039;,&#039;2013.11.22-18:38:00&#039;,&#039;2018.11.22-18:38:00&#039;,\r\n  4  113,0,3,&#039;2013.12.20-14:53:29&#039;,&#039;4LHHItaSMfB1q5HS8oX1KA==&gt;&#039;,&#039;0&#039;);\r\ninsert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)\r\n*\r\nERROR at line 1:\r\nORA-00001: unique constraint (YALLONKING.TBLCERTREQ_INDEX02) violated\r\n<\/pre>\n<p>\u7ee7\u7eed\u67e5\u770b\u5c06\u8981\u63d2\u5165\u7684\u6761\u76ee\u7684\u4e3b\u952eref\u662f\u5426\u548c\u5df2\u5b58\u5728\u7684\u8bb0\u5f55\u91cd\u590d\u4e86<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nSQL&gt; select * from tblcertreq where ref=&#039;08f03e7a3793acd295e1366c41dc5cag&#039;;\r\n\r\nno rows selected\t\t\t&lt;-\u53d1\u73b0\u4e3b\u952e\u4e0d\u5b58\u5728\u91cd\u590d\r\n\r\nSQL&gt; select * from tblcertreq where dn=&#039;CN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA Operation CA2,C=CN&#039;;\r\n\r\nno rows selected\t\t\t&lt;-\u53d1\u73b0\u8be5DN\u4e5f\u4e0d\u5b58\u5728\r\n\r\n<\/pre>\n<p>\u4f46\u662f\u53d1\u73b0\u63d2\u5165USERSTATUS=5\u5374\u662f\u53ef\u4ee5\u7684<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nSQL&gt; insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)\r\n  2  values(&#039;08f03e7a3793acd295e1366c41dc5cag&#039;,&#039;AnszcgexrSoXfzgiZ\/0cj9h\/mtUZrvfCYM9GQnT8OWTpnAEqUn\/U6Uq4RHJSYYbj&gt;&#039;,\r\n  3  &#039;CN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA Operation CA2,C=CN&#039;,&#039;2013.11.22-18:38:00&#039;,&#039;2018.11.22-18:38:00&#039;,\r\n  4  113,0,5,&#039;2013.12.20-14:53:29&#039;,&#039;4LHHItaSMfB1q5HS8oX1KA==&gt;&#039;,&#039;0&#039;);\r\n\r\n1 row created.\r\n\r\nSQL&gt; rollback;\r\n\r\n<\/pre>\n<p>\u56de\u6eda\u63895\u540e\u7ee7\u7eed\u63d2\u5165<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nSQL&gt; insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)\r\n  2  values(&#039;08f03e7a3793acd295e1366c41dc5cag&#039;,&#039;AnszcgexrSoXfzgiZ\/0cj9h\/mtUZrvfCYM9GQnT8OWTpnAEqUn\/U6Uq4RHJSYYbj&gt;&#039;,\r\n  3  &#039;CN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA Operation CA2,C=CN&#039;,&#039;2013.11.22-18:38:00&#039;,&#039;2018.11.22-18:38:00&#039;,\r\n  4  113,0,3,&#039;2013.12.20-14:53:29&#039;,&#039;4LHHItaSMfB1q5HS8oX1KA==&gt;&#039;,&#039;0&#039;);\r\n\/\/\u6b64\u5904\u7ee7\u7eedhang\u4f4f\r\n<\/pre>\n<p>\u7b2c\u4e09\u4e2a\u4f1a\u8bdd(session id:36)<br \/>\n\u5728\u8be5\u56de\u8bdd\u4e2d\u67e5\u770b\u9501\u76f8\u5173\u4fe1\u606f<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nSQL&gt; select sid from v$mystat where rownum&lt;2;\r\n\r\n       SID\r\n----------\r\n        36\r\nSQL&gt; sho user\r\nUSER is &quot;SYS&quot;\r\nSQL&gt; SELECT inst_id,DECODE(request, 0, &#039;Holder :&#039;, &#039;Waiter :&#039;) || sid sess,\r\n  2         id1,\r\n  3         id2,\r\n  4         lmode,\r\n  5         2 request,\r\n  6         type\r\n  7    FROM gV$LOCK\r\n  8   WHERE (id1, id2, type) IN\r\n  9         (SELECT id1, id2, type FROM V$LOCK WHERE request &gt; 0)\r\n 10   ORDER BY inst_id,id1, request;\r\n\r\n   INST_ID SESS                                                    ID1        ID2      LMODE    REQUEST TY\r\n---------- ------------------------------------------------ ---------- ---------- ---------- ---------- --\r\n         1 Waiter :33                                           196614        706          0          2 TX\r\n         1 Holder :29                                           196614        706          6          2 TX\r\n\r\n<\/pre>\n<p>\u6b64\u5904\u53d1\u73b0\u786e\u5b9e\u662f\u4f1a\u8bdd33\u5728\u7b49\u5f85\u4f1a\u8bdd29\u91ca\u653e\u9501<br \/>\n\u6ce8\uff1a\tTX - Transaction enqueue<br \/>\n\t\tLMODE 6 - exclusive (X)<br \/>\n\t\tREQUEST 2 - row-S (SS)<br \/>\n\t\tID1:\u4ee5\u5341\u8fdb\u5236\u6570\u503c\u8868\u793a\u8be5\u4e8b\u52a1\u6240\u5360\u7528\u7684\u56de\u6eda\u6bb5\u53f7\u4e0e\u8be5\u4e8b\u52a1\u5728\u8be5\u56de\u6eda\u6bb5\u7684\u4e8b\u52a1\u8868\uff08Transaction table\uff09\u4e2d\u6240\u5360\u7528\u7684\u69fd\u53f7\uff08slot number\uff0c\u53ef\u7406\u89e3\u4e3a\u8bb0\u5f55\u53f7\uff09\u3002\u5176\u7ec4\u6210\u5f62\u5f0f\u4e3a\uff1a 0xRRRRSSSS ( RRRR = RBS number, SSSS = slot )\u3002<br \/>\n\t\tID2:\u4ee5\u5341\u8fdb\u5236\u6570\u503c\u8868\u793a\u73af\u7ed5\uff08wrap\uff09\u6b21\u6570\uff0c\u5373\u8be5\u69fd\uff08slot\uff09\u88ab\u91cd\u7528\u7684\u6b21\u6570\uff1b<\/p>\n<p>\u4ece\u4e0a\u8ff0\u67e5\u8be2\u7ed3\u679c\u53ef\u77e5\uff0c2\u4e2a\u4f1a\u8bdd\u4e89\u7528\u7684\u8d44\u6e90\u662f\u540c\u4e00\u56de\u6eda\u6bb5\u8d44\u6e90<\/p>\n<p>\u5c06\u4e0a\u8fb9\u7684ID1\u6362\u7b97\u5982\u4e0b<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nSQL&gt; select trunc(196614\/65536),mod(196614,65536) from dual;\r\n\r\nTRUNC(196614\/65536) MOD(196614,65536)\r\n------------------- -----------------\r\n                  3                 6\t\r\n<\/pre>\n<p>\u8bf4\u660e\uff1a\u4e89\u7528\u7684\u662f3\u53f7\u56de\u6eda\u6bb5\u76846\u53f7\u4e8b\u52a1\u69fd<\/p>\n<p>\u67e5\u8be2\u4e8b\u52a1\u8868\u83b7\u5f97\u66f4\u8be6\u7ec6\u4fe1\u606f<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nSQL&gt; select ADDR,XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC,STATUS from v$transaction;\r\n\r\nADDR                 XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC STATUS\r\n---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------\r\n0000000095A18098          6         32        889          3        535        181         31 ACTIVE\r\n0000000095A753F8          3          6        706          3        322        155         22 ACTIVE\r\n\r\nSQL&gt; select FILE_ID, FILE_NAME,TABLESPACE_NAME from dba_data_files where FILE_ID=3;\r\n\r\n   FILE_ID FILE_NAME                                          TABLESPACE_NAME\r\n---------- -------------------------------------------------- --------------------\r\n         3 \/u01\/app\/oracle\/oradata\/ora11gr2\/undotbs01.dbf     UNDOTBS1\r\n<\/pre>\n<p>\u8bf4\u660e\uff1a\u4ece\u4e0a\u8fb9\u8fd9\u4e2a\u67e5\u8be2\u53ef\u4ee5\u770b\u5230\u5177\u4f53\u4e89\u7528\u7684\u662f\u4f4d\u4e8e3\u53f7\u6570\u636e\u6587\u4ef6(\u4e5f\u5c31\u662fUNDO\u6570\u636e\u6587\u4ef6,322\u53f7\u6570\u636e\u5757\u4e0a)\u76843\u53f7\u56de\u6eda\u6bb5\u76846\u53f7\u4e8b\u52a1\u69fd\u7684\u7b2c706\u6b21\u8bb0\u5f55<\/p>\n<p>\u8f6c\u50a83\u53f7\u56de\u6eda\u6bb5\u7684UNDO\u6bb5\u5934<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nSQL&gt; select * from V$ROLLNAME where USN=3;\r\n\r\n       USN NAME\r\n---------- ------------------------------\r\n         3 _SYSSMU3_307413221$\r\n\t\t \r\nSQL&gt; oradebug setmypid\r\nStatement processed.\r\nSQL&gt; alter system dump undo header &#039;_SYSSMU3_307413221$&#039;;\r\n\r\nSystem altered.\r\n\r\nSQL&gt; oradebug tracefile_name\r\n\/u01\/app\/oracle\/diag\/rdbms\/ora11gr2\/ora11gr2\/trace\/ora11gr2_ora_2310.trc\r\nSQL&gt; oradebug close_trace\r\nStatement processed.\r\n\r\n<\/pre>\n<p>trace\u4e2d\u5173\u4e8e\u4e8b\u52a1\u8868\u4fe1\u606f\u5982\u4e0b\uff1a<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\n  TRN TBL::\r\n \r\n  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt\r\n  ------------------------------------------------------------------------------------------------\r\n   0x00    9    0x00  0x02c2  0x0013  0x0000.000eab9c  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927\r\n   0x01    9    0x00  0x02c2  0x0019  0x0000.000eacba  0x00c00140  0x0000.000.00000000  0x00000001   0x00000000  1387368974\r\n   0x02    9    0x00  0x02c3  0x000c  0x0000.000eae5a  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1387370017\r\n   0x03    9    0x00  0x02c1  0x0016  0x0000.000eae97  0x00c00142  0x0000.000.00000000  0x00000001   0x00000000  1387370127\r\n   0x04    9    0x00  0x02c2  0x0002  0x0000.000eae0a  0x00c00141  0x0000.000.00000000  0x00000001   0x00000000  1387369828\r\n   0x05    9    0x00  0x02c3  0x0001  0x0000.000eac7d  0x00c00140  0x0000.000.00000000  0x00000003   0x00000000  1387368958\r\n   0x06   10    0x80  0x02c2  0x0005  0x0000.000eafa7  0x00c00142  0x0000.000.00000000  0x00000001   0x00000000  0\r\n   0x07    9    0x00  0x02c4  0x000e  0x0000.000eaeaa  0x00c00142  0x0000.000.00000000  0x00000001   0x00000000  1387370127\r\n   0x08    9    0x00  0x02c3  0x0012  0x0000.000eac26  0x00c0013a  0x0000.000.00000000  0x00000001   0x00000000  1387368927\r\n   0x09    9    0x00  0x02c3  0x0015  0x0000.000eab46  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927\r\n   0x0a    9    0x00  0x02c1  0x000b  0x0000.000eab23  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927\r\n   0x0b    9    0x00  0x02c3  0x0018  0x0000.000eab2d  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927\r\n   0x0c    9    0x00  0x02c2  0x0003  0x0000.000eae8e  0x00c00142  0x0000.000.00000000  0x00000001   0x00000000  1387370127\r\n   0x0d    9    0x00  0x02c1  0x001e  0x0000.000eab78  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927\r\n   0x0e    9    0x00  0x02c3  0x0017  0x0000.000eaeb3  0x00c00142  0x0000.000.00000000  0x00000001   0x00000000  1387370127\r\n   0x0f    9    0x00  0x02c1  0x001d  0x0000.000eab0f  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927\r\n   0x10    9    0x00  0x02c4  0x0008  0x0000.000eac19  0x00c0013a  0x0000.000.00000000  0x00000001   0x00000000  1387368927\r\n   0x11    9    0x00  0x02c4  0x001a  0x0000.000eabbd  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927\r\n   0x12    9    0x00  0x02c3  0x0005  0x0000.000eac33  0x00c0013a  0x0000.000.00000000  0x00000001   0x00000000  1387368928\r\n   0x13    9    0x00  0x02c3  0x0011  0x0000.000eabae  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927\r\n   0x14    9    0x00  0x02c0  0x0021  0x0000.000eabd9  0x00c0013a  0x0000.000.00000000  0x00000001   0x00000000  1387368927\r\n   0x15    9    0x00  0x02c1  0x001f  0x0000.000eab57  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927\r\n   0x16    9    0x00  0x02c2  0x0007  0x0000.000eaea0  0x00c00142  0x0000.000.00000000  0x00000001   0x00000000  1387370127\r\n   0x17    9    0x00  0x02c4  0x0020  0x0000.000eaebc  0x00c00142  0x0000.000.00000000  0x00000001   0x00000000  1387370127\r\n   0x18    9    0x00  0x02c1  0x0009  0x0000.000eab38  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927\r\n   0x19    9    0x00  0x02c2  0x0004  0x0000.000ead49  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1387369353\r\n   0x1a    9    0x00  0x02c2  0x0014  0x0000.000eabcb  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927\r\n   0x1b    9    0x00  0x02c2  0x0010  0x0000.000eac07  0x00c0013a  0x0000.000.00000000  0x00000001   0x00000000  1387368927\r\n   0x1c    9    0x00  0x02c3  0x001b  0x0000.000eabf9  0x00c0013a  0x0000.000.00000000  0x00000001   0x00000000  1387368927\r\n   0x1d    9    0x00  0x02c1  0x000a  0x0000.000eab19  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927\r\n   0x1e    9    0x00  0x02c2  0x0000  0x0000.000eab89  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927\r\n   0x1f    9    0x00  0x02c1  0x000d  0x0000.000eab67  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927\r\n   0x20    9    0x00  0x02c3  0xffff  0x0000.000eaf30  0x00c00142  0x0000.000.00000000  0x00000001   0x00000000  1387370428\r\n   0x21    9    0x00  0x02c2  0x001c  0x0000.000eabe8  0x00c0013a  0x0000.000.00000000  0x00000001   0x00000000  1387368927\r\n<\/pre>\n<p>\u6ce8\uff1a\t11g\u4e2d\u8be5\u8868\u53ea\u80fd\u5b58\u50a834\uff080x21=33\uff09\u6761\u8bb0\u5f55\uff0c\u7136\u540e\u5c31\u8fdb\u884c\u8986\u76d6\u91cd\u7528<br \/>\n\t\tstate:9-incative,10-active<br \/>\n\u8bf4\u660e\uff1a\u53d1\u73b0\u53ea\u6709\u7b2c\u4e03\u4e2a\u8bb0\u5f55\uff080x06\uff09\u662f\u6d3b\u52a8\u7684\uff0c\u5176wrap#=0x02c2\uff08706\uff09<\/p>\n<p>\u628a\u8fd9\u4e2a\u5757dump\u51fa\u6765\u7ee7\u7eed\u5206\u6790<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nSQL&gt; oradebug setmypid\r\nStatement processed.\r\nSQL&gt; alter system dump datafile 3 block 322;\r\n\r\nSystem altered.\r\n\r\nSQL&gt; oradebug tracefile_name\r\n\/u01\/app\/oracle\/diag\/rdbms\/ora11gr2\/ora11gr2\/trace\/ora11gr2_ora_2310.trc\r\n<\/pre>\n<p>\u90e8\u5206trace\u6587\u4ef6\u5982\u4e0b\uff1a<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nUNDO BLK:  \r\nxid: 0x0003.006.000002c2  seq: 0x9b  cnt: 0x16  irb: 0x16  icl: 0x0   flg: 0x0000\r\n \r\n Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset\r\n---------------------------------------------------------------------------\r\n0x01 0x1f38     0x02 0x1e88     0x03 0x1e1c     0x04 0x1d6c     0x05 0x1cc0     \r\n0x06 0x1c38     0x07 0x1bd4     0x08 0x1b90     0x09 0x1b3c     0x0a 0x1af8     \r\n0x0b 0x1aa4     0x0c 0x1a1c     0x0d 0x19c0     0x0e 0x1930     0x0f 0x18f4     \r\n0x10 0x1894     0x11 0x1834     0x12 0x17e0     0x13 0x1770     0x14 0x170c     \r\n0x15 0x1664     0x16 0x15f0     \r\n\t\t... ...\r\n*-----------------------------\r\n* Rec #0x13  slt: 0x06  objn: 71495(0x00011747)  objd: 71495  tblspc: 5(0x00000005)\r\n*       Layer:  11 (Row)   opc: 1   rci 0x00   \r\nUndo type:  Regular undo    Begin trans    Last buffer split:  No \r\nTemp Object:  No \r\nTablespace Undo:  No \r\nrdba: 0x00000000Ext idx: 0\r\nflg2: 0\r\n*-----------------------------\r\nuba: 0x00c00142.009b.0e ctl max scn: 0x0000.000eaafb prv tx scn: 0x0000.000eab05\r\ntxn start scn: scn: 0x0000.000eaede logon user: 80\r\n prev brb: 12583230 prev bcl: 0\r\nKDO undo record:\r\nKTB Redo \r\nop: 0x03  ver: 0x01  \r\ncompat bit: 4 (post-11) padding: 1\r\nop: Z\r\nKDO Op code: DRP row dependencies Disabled\r\n  xtype: XA flags: 0x00000000  bdba: 0x0140000f  hdba: 0x0140000a\r\nitli: 2  ispac: 0  maxfr: 4858\r\ntabn: 0 slot: 5(0x5)\r\n \r\n*-----------------------------\r\n* Rec #0x14  slt: 0x06  objn: 71496(0x00011748)  objd: 71496  tblspc: 5(0x00000005)\r\n*       Layer:  10 (Index)   opc: 22   rci 0x13   \r\nUndo type:  Regular undo   Last buffer split:  No \r\nTemp Object:  No \r\nTablespace Undo:  No \r\nrdba: 0x00000000\r\n*-----------------------------\r\nindex undo for leaf key operations\r\nKTB Redo \r\nop: 0x03  ver: 0x01  \r\ncompat bit: 4 (post-11) padding: 1\r\nop: Z\r\nDump kdilk : itl=3, kdxlkflg=0x1 sdc=0 indexid=0x1400012 block=0x01400013\r\n(kdxlpu): purge leaf row\r\nkey :(33): \r\n 20 30 38 66 30 33 65 37 61 33 37 39 33 61 63 64 32 39 35 65 31 33 36 36 63\r\n 34 31 64 63 35 63 61 66\r\n \r\n*-----------------------------\r\n* Rec #0x15  slt: 0x06  objn: 71497(0x00011749)  objd: 71497  tblspc: 5(0x00000005)\r\n*       Layer:  10 (Index)   opc: 22   rci 0x14   \r\nUndo type:  Regular undo   Last buffer split:  No \r\nTemp Object:  No \r\nTablespace Undo:  No \r\nrdba: 0x00000000\r\n*-----------------------------\r\nindex undo for leaf key operations\r\nKTB Redo \r\nop: 0x04  ver: 0x01  \r\ncompat bit: 4 (post-11) padding: 1\r\nop: L  itl: xid:  0x000a.008.000002cf uba: 0x00c00118.00c1.39\r\n                      flg: C---    lkc:  0     scn: 0x0000.000eacc3\r\nDump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x140001a block=0x0140001b\r\n(kdxlpu): purge leaf row\r\nkey :(77): \r\n 43 43 4e 3d 39 35 35 38 30 31 30 30 34 32 31 35 38 38 38 35 2c 4f 55 3d 43\r\n 75 73 74 6f 6d 65 72 73 2c 4f 55 3d 59 55 5a 48 49 2c 4f 3d 43 46 43 41 20\r\n 4f 70 65 72 61 74 69 6f 6e 20 43 41 32 2c 43 3d 43 4e 03 c2 02 0e 02 c1 04\r\n 01 30\r\n \r\n*-----------------------------\r\n* Rec #0x16  slt: 0x06  objn: 71498(0x0001174a)  objd: 71498  tblspc: 5(0x00000005)\r\n*       Layer:  10 (Index)   opc: 22   rci 0x15   \r\nUndo type:  Regular undo   Last buffer split:  No \r\nTemp Object:  No \r\nTablespace Undo:  No \r\nrdba: 0x00000000\r\n*-----------------------------\r\nindex undo for leaf key operations\r\nKTB Redo \r\nop: 0x04  ver: 0x01  \r\ncompat bit: 4 (post-11) padding: 1\r\nop: L  itl: xid:  0x000a.008.000002cf uba: 0x00c00118.00c1.3a\r\n                      flg: C---    lkc:  0     scn: 0x0000.000eacc3\r\nDump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x1400022 block=0x01400023\r\n(kdxlpu): purge leaf row\r\nkey :(27): \r\n 13 32 30 31 33 2e 31 32 2e 32 30 2d 31 34 3a 35 33 3a 32 39 06 01 40 00 0f\r\n 00 05\r\n \r\nEnd dump data blocks tsn: 2 file#: 3 minblk 322 maxblk 322\r\n\r\n<\/pre>\n<p>\u67e5\u770b\u4e89\u7528\u7684\u8d44\u6e90<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nSQL&gt; select utl_raw.cast_to_varchar2(&#039;13323031332e31322e32302d31343a35333a3239060140000f0005&#039;) from dual;\r\n\r\nUTL_RAW.CAST_TO_VARCHAR2(&#039;13323031332E31322E32302D\r\n--------------------------------------------------\r\n2013.12.20-14:53:29@  \t\t&lt;-\u7531\u4e8e\u6d4b\u8bd5\u6570\u636e\u65e5\u671f\u76f8\u540c\uff0c\u65e0\u6cd5\u5206\u8fa8\r\n\t\t\r\n\t\t\r\nSQL&gt; select utl_raw.cast_to_varchar2(&#039;43434e3d393535383031303034323135383838352c4f553d437573746f6d6572732c4f553d59555a48492c4f3d43464341204f7065726174696f6e204341322c433d434e03c2020e02c1040130&#039;) from dual;\r\n\r\nUTL_RAW.CAST_TO_VARCHAR2(&#039;43434E3D3935353830313030\r\n--------------------------------------------------\r\nCCN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA\r\nOperation CA2,C=CN??0\t\t&lt;-\u7531\u4e8e\u6d4b\u8bd5\u6570\u636eDN\u76f8\u540c\uff0c\u65e0\u6cd5\u5206\u8fa8\r\n\r\n\r\nSQL&gt; select utl_raw.cast_to_varchar2(&#039;203038663033653761333739336163643239356531333636633431646335636166&#039;) from dual;\r\n\r\nUTL_RAW.CAST_TO_VARCHAR2(&#039;203038663033653761333739336163643239356531333636633431\r\n--------------------------------------------------------------------------------\r\n 08f03e7a3793acd295e1366c41dc5caf\t\t&lt;-\u6b64\u5904\u4e3a\u5177\u6709\u53ef\u8bc6\u522b\u6027\u7684\u7b2c\u4e00\u4e2a\u4f1a\u8bdd\u63d2\u5165\u7684ref(\u4e3b\u952e)\u503c\r\n<\/pre>\n<p>\u81f3\u6b64\uff0c\u53d1\u73b0\u8be5\u56de\u6eda\u6bb5\u7684\u4e8b\u7269\u69fd\u5b58\u653e\u7684\u7b2c\u4e00\u4e2a\u4e00\u76f4\u8fdf\u8fdf\u6ca1\u6709\u63d0\u4ea4\u7684\u4f1a\u8bdd\u7684\u8bb0\u5f55\u4fe1\u606f<\/p>\n<p>\u4e0b\u8fb9\u7ee7\u7eed\u7528bbed\u67e5\u770b\u5728\u7269\u7406\u6570\u636e\u5757\u4e2d\u7684\u5177\u4f53\u503c,\u8fdb\u4e00\u6b65\u786e\u8ba4\u6240\u4e89\u7528\u7684\u8d44\u6e90\u4fe1\u606f<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nBBED&gt; set dba 3,322\r\n        DBA             0x00c00142 (12583234 3,322)\r\n\r\nBBED&gt; find \/c 08f03e7a3793acd295e1366c41dc5cag\r\nBBED-00212: search string not found\r\n\r\n\r\nBBED&gt; find \/c 08f03e7a3793acd295e1366c41dc5caf\r\n File: \/u01\/app\/oracle\/oradata\/ora11gr2\/undotbs01.dbf (3)\r\n Block: 322              Offsets: 5985 to 6496           Dba:0x00c00142\r\n------------------------------------------------------------------------\r\n 30386630 33653761 33373933 61636432 39356531 33363663 34316463 35636166 \r\n 00000008 004c0008 00140047 17010047 17010005 00000000 0000000b 01060008 \r\n 0c010000 00000042 01c0009b 000e00fb aa0e0000 00290005 ab0e0000 0000009f \r\n aa0e00de ae0e0000 0001003e 01c00000 00000050 00000003 0d000008 0c01000f \r\n 0040010a 004001fa 12030102 00ffff05 0000000c 00180010 00140002 0008000d \r\n 0100000d 01000000 00000000 0000000a 16201100 00c30202 0d140018 00200042 \r\n 01c0009b 000e0012 02059780 07400081 07400000 00000000 0015ad01 80000000 \r\n 80ee4f00 00440008 00180020 0020000e 0100000c 01000001 00000000 0000000b \r\n 01201000 00c30204 0d0700c4 02000008 001000ca 020000fa 00c000ce 00040000 \r\n 800000c0 440c0085 b6800092 018000fa 120a0101 00000000 80ec5e00 000080ee \r\n 48000001 00000008 00180020 0020000e 0100000c 01000001 00000000 0000000b \r\n 01200f00 00c30204 0d0700c4 02000008 001000ca 020000fa 00c000ce 00020000 \r\n 800000c0 440c005e ec800092 018000fa 120a0101 000000ac 0101ad85 b60080b6 \r\n 85000002 00000008 00180008 0014000e 0100000c 01000001 00000000 0000000b \r\n 01200e00 00c30203 0d0000a5 ae0e0048 ee800092 018000fa 12030101 00000000 \r\n 0000ad0a 004c0020 00140002 0000000d 0100000d 01000000 00000000 0000000a \r\n\r\n &lt;32 bytes per line&gt;\r\n\r\nBBED&gt; dump \/v dba 3,322 offset 5985 count 32\r\n File: \/u01\/app\/oracle\/oradata\/ora11gr2\/undotbs01.dbf (3)\r\n Block: 322     Offsets: 5985 to 6016  Dba:0x00c00142\r\n-------------------------------------------------------\r\n 30386630 33653761 33373933 61636432 l 08f03e7a3793acd2\r\n 39356531 33363663 34316463 35636166 l 95e1366c41dc5caf\t\r\n\r\n &lt;16 bytes per line&gt;\r\n \r\n<\/pre>\n<p>\u4ee5\u4e0a\u5747\u786e\u8ba4\u4e86\u4e89\u7528\u7684\u8d44\u6e90\u662f\u4f1a\u8bdd\u4e00\u6301\u6709\u76843\u53f7\u6570\u636e\u6587\u4ef6\u76843\u53f7\u56de\u6eda\u6bb5\u76846\u53f7\u4e8b\u52a1\u69fd\u7684\u7b2c706\u6b21\u8bb0\u5f55\u4f4d\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u552f\u4e00\u6027\u7ec4\u5408\u7d22\u5f15\u5f15\u8d77\u7684\u63d2\u5165\u7684sql hang\u4f4f\u7684\u5206\u6790 \u95ee\u9898\u5206\u6790\uff1a\u62e5\u6709\u76f8\u540c\u7ec4\u5408\u7d22\u5f15\u5217\u952e\u503c\u7684sql\uff0c\u5728\u6279\u91cf\u64cd\u4f5c\u65f6\u53ef\u80fd [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[36],"tags":[222,250,251],"_links":{"self":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/1168"}],"collection":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1168"}],"version-history":[{"count":3,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/1168\/revisions"}],"predecessor-version":[{"id":1171,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/1168\/revisions\/1171"}],"wp:attachment":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1168"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1168"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1168"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}