{"id":2878,"date":"2023-02-21T17:25:31","date_gmt":"2023-02-21T09:25:31","guid":{"rendered":"http:\/\/dba.qishuo.xin\/?p=2878"},"modified":"2023-02-21T17:28:28","modified_gmt":"2023-02-21T09:28:28","slug":"%e5%a6%82%e4%bd%95%e5%9c%a8oracle%e4%b8%ad%e6%9f%a5%e6%89%be%e9%94%81%e5%ae%9a%e7%9a%84%e8%a1%8c","status":"publish","type":"post","link":"http:\/\/dba.qishuo.xin\/?p=2878","title":{"rendered":"\u5982\u4f55\u5728Oracle\u4e2d\u67e5\u627e\u9501\u5b9a\u7684\u884c"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_76 ez-toc-wrap-right counter-hierarchy ez-toc-counter ez-toc-light-blue ez-toc-container-direction\">\n<label for=\"ez-toc-cssicon-toggle-item-6a020c15d1dbe\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a020c15d1dbe\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"http:\/\/dba.qishuo.xin\/?p=2878\/#1_%E6%9F%A5%E8%AF%A2%E6%95%B0%E6%8D%AE%E5%BA%93%E4%B8%AD%E9%94%81%E4%BF%A1%E6%81%AF\" >1. \u67e5\u8be2\u6570\u636e\u5e93\u4e2d\u9501\u4fe1\u606f<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"http:\/\/dba.qishuo.xin\/?p=2878\/#2_%E6%9F%A5%E8%AF%A2%E4%BA%8B%E5%8A%A1%E9%98%BB%E5%A1%9E%E6%BA%90%E5%A4%B4%E7%9A%84%E4%BC%9A%E8%AF%9DID\" >2. \u67e5\u8be2\u4e8b\u52a1\u963b\u585e\u6e90\u5934\u7684\u4f1a\u8bddID<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"http:\/\/dba.qishuo.xin\/?p=2878\/#3_%E7%BB%93%E5%90%88%E9%94%81%E4%BC%9A%E8%AF%9DID%E4%B8%8E%E9%94%81%E7%9A%84%E5%AF%B9%E8%B1%A1%EF%BC%8C%E6%9F%A5%E8%AF%A2%E9%94%81%E5%AF%B9%E8%B1%A1%E4%B8%AD%E5%85%B7%E4%BD%93%E8%A2%AB%E9%94%81%E5%AE%9A%E7%9A%84%E8%A1%8C\" >3. \u7ed3\u5408\u9501\u4f1a\u8bddID\u4e0e\u9501\u7684\u5bf9\u8c61\uff0c\u67e5\u8be2\u9501\u5bf9\u8c61\u4e2d\u5177\u4f53\u88ab\u9501\u5b9a\u7684\u884c<\/a><\/li><\/ul><\/nav><\/div>\n<h4><span class=\"ez-toc-section\" id=\"1_%E6%9F%A5%E8%AF%A2%E6%95%B0%E6%8D%AE%E5%BA%93%E4%B8%AD%E9%94%81%E4%BF%A1%E6%81%AF\"><\/span>1. \u67e5\u8be2\u6570\u636e\u5e93\u4e2d\u9501\u4fe1\u606f<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">-- 1. \u67e5\u8be2\u9501\u5bf9\u8c61\u76f8\u5173\u7684\u5c5e\u6027\uff1a\nSELECT glo.inst_id,      -- \u9501\u5b58\u5728\u7684\u5b9e\u4f8bID\n       glo.session_id,   -- \u9501\u5b58\u5728\u7684\u4f1a\u8bddID\n       o.owner,          -- \u9501\u5bf9\u8c61\u62e5\u6709\u8005\n       glo.object_id,    -- \u9501\u5bf9\u8c61ID\n       DECODE(gl.type,&#039;TM&#039;, o.object_name, NULL) object_name,   -- \u9501\u5bf9\u8c61\u540d\u79f0\n       o.subobject_name,    -- \u5b50\u5bf9\u8c61\u540d\u5b57\uff0c\u4f8b\u5982\u5206\u533a\u540d\u79f0\n       o.object_type,       -- \u5bf9\u8c61\u7c7b\u578b\uff1a\u8868\u3001\u5206\u533a\u7b49\n       gl.type,    -- \u9501\u7684\u7c7b\u578b\n       DECODE(gl.lmode, 0,&#039;0:none&#039;,\n                        1,&#039;1:NULL&#039;,\n                        2,&#039;2:SS(Row-Share)&#039;,\n                        3,&#039;3:SX(Row-X)&#039;,\n                        4,&#039;4:S(Share)&#039;,\n                        5,&#039;5:SSX(S\/Row-X)&#039;,\n                        6,&#039;6:X(Exclusive)&#039;) lmode, -- \u4f1a\u8bdd\u6301\u6709\u9501\u7684\u9501\u5b9a\u6a21\u5f0f\n       DECODE(gl.request, 0,&#039;0:none&#039;,\n                         1,&#039;1:NULL&#039;,\n                         2,&#039;2:SS(Row-Share)&#039;,\n                         3,&#039;3:SX(Row-X)&#039;,\n                         4,&#039;4:S(Share)&#039;,\n                         5,&#039;5:SSX(S\/Row-X)&#039;,\n                         6,&#039;6:X(Exclusive)&#039;)  request, -- \u8fdb\u7a0b\u8bf7\u6c42\u9501\u5b9a\u7684\u9501\u5b9a\u6a21\u5f0f\n       SYSDATE - gl.ctime\/60\/60\/24 AS lock_start_time, -- \u9501\u5f00\u59cb\u7684\u65f6\u95f4\n       ROUND(gl.ctime\/60,0) locked_min   -- \u9501\u5b9a\u7684\u65f6\u95f4(\u5206\u949f)\n  FROM gv$locked_object glo, gv$lock gl,dba_objects o\n WHERE glo.inst_id = gl.inst_id\n   AND glo.session_id = gl.sid\n   AND glo.object_id = gl.id1\n   AND o.object_id(+) = gl.id1\n   -- AND glo.session_id = &amp;session_id\n ORDER BY gl.ctime DESC;<\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"2_%E6%9F%A5%E8%AF%A2%E4%BA%8B%E5%8A%A1%E9%98%BB%E5%A1%9E%E6%BA%90%E5%A4%B4%E7%9A%84%E4%BC%9A%E8%AF%9DID\"><\/span>2. \u67e5\u8be2\u4e8b\u52a1\u963b\u585e\u6e90\u5934\u7684\u4f1a\u8bddID<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\"> SELECT r.root_sid,\n        s.serial#,\n        r.blocked_num,\n        TRUNC(r.avg_wait_seconds) AS avg_wait_seconds,\n        s.username,s.status,\n        s.event,s.MACHINE,\n        s.PROGRAM,\n        s.sql_id,\n        s.prev_sql_id\n   FROM (SELECT root_sid,\n                AVG(seconds_in_wait) AS avg_wait_seconds,\n                COUNT(*) - 1 AS blocked_num\n          FROM (SELECT CONNECT_BY_ROOT sid AS root_sid,\n                       seconds_in_wait\n                  FROM v$session\n                 START WITH blocking_session IS NULL\n               CONNECT BY PRIOR SID = blocking_session)\n                 GROUP BY root_sid HAVING COUNT(*) &gt; 1\n         ) r, v$session s\n   WHERE r.root_sid = s.sid;<\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"3_%E7%BB%93%E5%90%88%E9%94%81%E4%BC%9A%E8%AF%9DID%E4%B8%8E%E9%94%81%E7%9A%84%E5%AF%B9%E8%B1%A1%EF%BC%8C%E6%9F%A5%E8%AF%A2%E9%94%81%E5%AF%B9%E8%B1%A1%E4%B8%AD%E5%85%B7%E4%BD%93%E8%A2%AB%E9%94%81%E5%AE%9A%E7%9A%84%E8%A1%8C\"><\/span>3. \u7ed3\u5408\u9501\u4f1a\u8bddID\u4e0e\u9501\u7684\u5bf9\u8c61\uff0c\u67e5\u8be2\u9501\u5bf9\u8c61\u4e2d\u5177\u4f53\u88ab\u9501\u5b9a\u7684\u884c<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-sql\">CREATE OR REPLACE PROCEDURE locksmith(table_name VARCHAR2) IS\n  TYPE tabcurtype IS REF CURSOR;\n  TYPE tabrowstype IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;\n  table_cursor tabcurtype;\n  rowid_table  tabrowstype;\n  row_id       ROWID;\n  status       NUMBER;\n  aud_sid      NUMBER;\n  test_sid     NUMBER;\n  test_serial  NUMBER;\n  locker_sid   NUMBER;\n  locker_name  VARCHAR2(1000);\n  message      VARCHAR2(1000);\n\n  resource_busy EXCEPTION;\n  PRAGMA EXCEPTION_INIT(resource_busy, -54);\n\nBEGIN\n  rowid_table(0) := &#039;0&#039;;\n  dbms_output.enable(1000000);\n\n  OPEN table_cursor FOR &#039;select rowid from &#039; || table_name;\n  LOOP\n    BEGIN\n      FETCH table_cursor INTO row_id;\n      EXIT WHEN table_cursor%NOTFOUND;\n      SAVEPOINT one_register;\n\n      EXECUTE IMMEDIATE &#039;select 1 from &#039; || table_name || &#039; where rowid =:r for update nowait&#039; USING row_id;\n\n    EXCEPTION\n      WHEN resource_busy THEN\n        rowid_table(rowid_table.last + 1) := row_id;\n    END;\n\n    ROLLBACK TO SAVEPOINT one_register;\n  END LOOP;\n\n  CLOSE table_cursor;\n\n  FOR r IN rowid_table.first + 1 .. rowid_table.last\n    LOOP\n      dbms_output.put_line(rowid_table(r));\n    END LOOP;\n  ROLLBACK;\nEND;<\/code><\/pre>\n<p><strong>\u6267\u884c\u4e0a\u8ff0\u5b58\u50a8\u8fc7\u7a0b\uff0c\u83b7\u5f97\u8868\u4e2d\u88ab\u9501\u5b9a\u7684\u884c\u3002<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>1. \u67e5\u8be2\u6570\u636e\u5e93\u4e2d\u9501\u4fe1\u606f &#8212; 1. \u67e5\u8be2\u9501\u5bf9\u8c61\u76f8&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,21],"tags":[16,131],"class_list":{"0":"post-2878","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"hentry","6":"category-oracle","8":"tag-oracle","9":"tag-131"},"_links":{"self":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts\/2878","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2878"}],"version-history":[{"count":17,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts\/2878\/revisions"}],"predecessor-version":[{"id":2898,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts\/2878\/revisions\/2898"}],"wp:attachment":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2878"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2878"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2878"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}