<\/p>\n
create or replace view HAISINS_LOCK as\r\n select \r\n\tsid session_id,\r\n\tdecode(type, \r\n\t\t'TX', 'Transaction',\r\n\t\t'TM', 'DML',\r\n\t\t'DX', 'Distributed Xaction',\r\n\t\ttype) lock_type,\r\n\tdecode(lmode, \r\n\t\t0, 'None', \/* Mon Lock equivalent *\/\r\n\t\t1, 'Null', \/* N *\/\r\n\t\t2, 'Row-S (SS)', \/* L *\/\r\n\t\t3, 'Row-X (SX)', \/* R *\/\r\n\t\t4, 'Share', \/* S *\/\r\n\t\t5, 'S\/Row-X (SSX)', \/* C *\/\r\n\t\t6, 'Exclusive', \/* X *\/\r\n\t\tto_char(lmode)) mode_held,\r\n decode(request,\r\n\t\t0, 'None', \/* Mon Lock equivalent *\/\r\n\t\t1, 'Null', \/* N *\/\r\n\t\t2, 'Row-S (SS)', \/* L *\/\r\n\t\t3, 'Row-X (SX)', \/* R *\/\r\n\t\t4, 'Share', \/* S *\/\r\n\t\t5, 'S\/Row-X (SSX)', \/* C *\/\r\n\t\t6, 'Exclusive', \/* X *\/\r\n\t\tto_char(request)) mode_requested,\r\n to_char(id1) lock_id1, to_char(id2) lock_id2,\r\n\t decode(block,\r\n\t 0, 'Not Blocking', \/* Not blocking any other processes *\/\r\n\t\t1, 'Blocking', \/* This lock blocks other processes *\/\r\n\t\t2, 'Global', \/* This lock is global, so we can't tell *\/\r\n\t\tto_char(block)) blocking_others\r\n from v$lock\r\n where type = 'TX' \r\n or type = 'TM';\r\n\r\n\r\ndrop table HAISINS_holders;\r\n\r\ncreate table HAISINS_HOLDERS \/* temporary table *\/\r\n(\r\n waiting_session number,\r\n holding_session number,\r\n lock_type varchar2(26),\r\n mode_held varchar2(14),\r\n mode_requested varchar2(14),\r\n lock_table varchar2(22),\r\n lock_owner varchar2(30)\r\n);\r\n\r\n\r\ninsert into HAISINS_holders\r\n select w.session_id,\r\n h.session_id,\r\n w.lock_type,\r\n nvl(h.mode_held, w.mode_held),\r\n w.mode_requested,\r\n o.object_name,\r\n o.owner\r\n from (select session_id, lock_type, mode_held, lock_id1, lock_id2\r\n from HAISINS_lock \r\n where blocking_others = 'Blocking'\r\n and mode_held != 'None'\r\n and mode_held != 'Null' ) h, \r\n (select session_id, lock_type, mode_held, mode_requested, \r\n lock_id1, lock_id2\r\n from HAISINS_lock\r\n where lock_type = 'Transaction') w, \r\n HAISINS_lock a, dba_objects o\r\n where w.lock_type = h.lock_type(+)\r\n and w.lock_id1 = h.lock_id1(+)\r\n and w.lock_id2 = h.lock_id2(+)\r\n and w.session_id = a.session_id\r\n and a.lock_type = 'DML'\r\n and a.lock_id1 = o.object_id;\r\ncommit;\r\n\r\ninsert into HAISINS_holders\r\n select holding_session, null, 'None', null, null, null, null\r\n from HAISINS_holders\r\n minus\r\n select waiting_session, null, 'None', null, null, null, null\r\n from HAISINS_holders;\r\ncommit;\r\n\r\ncol lock_type format a12\r\ncol lock_owner format a8\r\ncol lock_table format a10\r\n\r\nselect lpad(' ',3*(level-1)) || waiting_session waiting_session,\r\n lock_type,\r\n mode_requested,\r\n mode_held,\r\n lock_table, \r\n lock_owner\r\n from HAISINS_holders\r\nconnect by prior waiting_session = holding_session\r\n start with holding_session is null;\r\n\r\nselect * from HAISINS_holders;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":" create or replace view HAISINS_LOCK as select sid session_id, decode(type, ‘TX’, ‘Transaction’, ‘TM’, ‘DML’, ‘DX’, ‘Distributed Xaction’, type) lock_type, decode(lmode, 0, ‘None’, \/* Mon Lock equivalent *\/ 1, ‘Null’, \/* N *\/ 2, ‘Row-S (SS)’, \/* L *\/ 3, ‘Row-X (SX)’, \/* R *\/ 4, ‘Share’, \/* S *\/ 5, ‘S\/Row-X (SSX)’, \/* C […]<\/p>\n","protected":false},"author":1,"featured_media":2880,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[3],"tags":[526,152,1038,1036,1037,153],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/02\/tips-newsletter.jpg?fit=680%2C530","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3619"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3619"}],"version-history":[{"count":1,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3619\/revisions"}],"predecessor-version":[{"id":3620,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3619\/revisions\/3620"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/2880"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3619"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3619"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3619"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}