{"id":44,"date":"2012-03-01T02:18:15","date_gmt":"2012-03-01T02:18:15","guid":{"rendered":"http:\/\/oranotes.tuktaroff.com\/?page_id=44"},"modified":"2012-03-01T23:07:31","modified_gmt":"2012-03-01T23:07:31","slug":"dml-locks-advanced","status":"publish","type":"page","link":"http:\/\/oranotes.tuktaroff.com\/?page_id=44","title":{"rendered":"DML locks &#8211; advanced"},"content":{"rendered":"<p><code><br \/>\n<script type=\"text\/javascript\"> \nfunction copy(what) {\n    window.clipboardData.setData('Text',what.value);\n}\nfunction paste(what) {\n    what.value = window.clipboardData.getData('Text');\n}\n<\/script><\/p>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nSET LINESIZE 165<br \/>\nSET PAGESIZE 66<\/p>\n<p>COLUMN oracle_user     FORMAT a15      HEADING 'Oracle User'<br \/>\nCOLUMN usercode        FORMAT a12      HEADING 'SID\/Serial#'<br \/>\nCOLUMN os_user         FORMAT a10      HEADING 'O\/S User'<br \/>\nCOLUMN program         FORMAT a25      HEADING 'Program'<br \/>\nCOLUMN mode_held       FORMAT a15      HEADING 'Mode Held'<br \/>\nCOLUMN mode_requested  FORMAT a15      HEADING 'Mode Requested'<br \/>\nCOLUMN lock_type       FORMAT a15      HEADING 'Lock Type'<br \/>\nCOLUMN object_name     FORMAT a30      HEADING 'Object Name'<br \/>\nCOLUMN lock_time_min   FORMAT 999,999  HEADING 'Lock Time (min)'<\/p>\n<p>SELECT<br \/>\n    s.username                                 oracle_user<br \/>\n  , l.sid || '\/' || s.serial#                  usercode<br \/>\n  , s.osuser                                   os_user<br \/>\n  , s.program                                  program<br \/>\n  , DECODE(l.lmode,<br \/>\n       1, NULL,<br \/>\n       2, 'Row Share',<br \/>\n       3, 'Row Exclusive',<br \/>\n       4, 'Share',<br \/>\n       5, 'Share Row Exclusive',<br \/>\n       6, 'Exclusive', 'None')                 mode_held<br \/>\n  , DECODE(l.request,<br \/>\n       1, NULL,<br \/>\n       2, 'Row Share',<br \/>\n       3, 'Row Exclusive',<br \/>\n       4, 'Share',<br \/>\n       5, 'Share Row Exclusive',<br \/>\n       6, 'Exclusive', 'None')                 mode_requested<br \/>\n  , DECODE(l.type,<br \/>\n       'MR', 'Media Recovery',<br \/>\n       'RT', 'Redo Thread',<br \/>\n       'UN', 'User Name',<br \/>\n       'TX', 'Transaction',<br \/>\n       'TM', 'DML',<br \/>\n       'UL', 'PL\/SQL User Lock',<br \/>\n       'DX', 'Distributed Xaction',<br \/>\n       'CF', 'Control File',<br \/>\n       'IS', 'Instance State',<br \/>\n       'FS', 'File Set',<br \/>\n       'IR', 'Instance Recovery',<br \/>\n       'ST', 'Disk Space Transaction',<br \/>\n       'TS', 'Temp Segment',<br \/>\n       'IV', 'Library Cache Invalidation',<br \/>\n       'LS', 'Log Start or Log Switch',<br \/>\n       'RW', 'Row Wait',<br \/>\n       'SQ', 'Sequence Number',<br \/>\n       'TE', 'Extend Table',<br \/>\n       'TT', 'Temp Table',<br \/>\n       l.type)                                 lock_type<br \/>\n  , o.owner || '.' || o.object_name<br \/>\n    || ' - (' || o.object_type || ')'          object_name<br \/>\n  , ROUND(l.ctime\/60, 2)                       lock_time_min<br \/>\nFROM<br \/>\n    v$session     s<br \/>\n  , v$lock        l<br \/>\n  , dba_objects   o<br \/>\n  , dba_tables    t<br \/>\nWHERE<br \/>\n      l.id1            =  o.object_id<br \/>\n  AND s.sid            =  l.sid<br \/>\n  AND o.owner          =  t.owner<br \/>\n  AND o.object_name    =  t.table_name<br \/>\n  AND o.owner          <> 'SYS'<br \/>\n  AND l.type           =  'TM'<br \/>\nORDER BY<br \/>\n  1<br \/>\n\/<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<pre class=\"qcode\">\r\nSET LINESIZE 165\r\nSET PAGESIZE 66\r\n\r\nCOLUMN oracle_user     FORMAT a15      HEADING 'Oracle User'\r\nCOLUMN usercode        FORMAT a12      HEADING 'SID\/Serial#' \r\nCOLUMN os_user         FORMAT a10      HEADING 'O\/S User'\r\nCOLUMN program         FORMAT a25      HEADING 'Program'\r\nCOLUMN mode_held       FORMAT a15      HEADING 'Mode Held'\r\nCOLUMN mode_requested  FORMAT a15      HEADING 'Mode Requested'\r\nCOLUMN lock_type       FORMAT a15      HEADING 'Lock Type'\r\nCOLUMN object_name     FORMAT a30      HEADING 'Object Name'\r\nCOLUMN lock_time_min   FORMAT 999,999  HEADING 'Lock Time (min)'\r\n\r\nSELECT\r\n    s.username                                 oracle_user\r\n  , l.sid || '\/' || s.serial#                  usercode\r\n  , s.osuser                                   os_user\r\n  , s.program                                  program\r\n  , DECODE(l.lmode,\r\n       1, NULL,\r\n       2, 'Row Share',\r\n       3, 'Row Exclusive',\r\n       4, 'Share',\r\n       5, 'Share Row Exclusive',\r\n       6, 'Exclusive', 'None')                 mode_held\r\n  , DECODE(l.request,\r\n       1, NULL,\r\n       2, 'Row Share',\r\n       3, 'Row Exclusive',\r\n       4, 'Share',\r\n       5, 'Share Row Exclusive',\r\n       6, 'Exclusive', 'None')                 mode_requested\r\n  , DECODE(l.type,\r\n       'MR', 'Media Recovery',\r\n       'RT', 'Redo Thread',\r\n       'UN', 'User Name',\r\n       'TX', 'Transaction',\r\n       'TM', 'DML',\r\n       'UL', 'PL\/SQL User Lock',\r\n       'DX', 'Distributed Xaction',\r\n       'CF', 'Control File',\r\n       'IS', 'Instance State',\r\n       'FS', 'File Set',\r\n       'IR', 'Instance Recovery',\r\n       'ST', 'Disk Space Transaction',\r\n       'TS', 'Temp Segment',\r\n       'IV', 'Library Cache Invalidation',\r\n       'LS', 'Log Start or Log Switch',\r\n       'RW', 'Row Wait',\r\n       'SQ', 'Sequence Number',\r\n       'TE', 'Extend Table',\r\n       'TT', 'Temp Table',\r\n       l.type)                                 lock_type\r\n  , o.owner || '.' || o.object_name \r\n    || ' - (' || o.object_type || ')'          object_name\r\n  , ROUND(l.ctime\/60, 2)                       lock_time_min\r\nFROM\r\n    v$session     s\r\n  , v$lock        l\r\n  , dba_objects   o\r\n  , dba_tables    t\r\nWHERE\r\n      l.id1            =  o.object_id \r\n  AND s.sid            =  l.sid\r\n  AND o.owner          =  t.owner\r\n  AND o.object_name    =  t.table_name\r\n  AND o.owner          <> 'SYS'\r\n  AND l.type           =  'TM'\r\nORDER BY\r\n  1\r\n\/\r\n<\/pre>\n<p><\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>SET LINESIZE 165 SET PAGESIZE 66 COLUMN oracle_user FORMAT a15 HEADING &#8216;Oracle User&#8217; COLUMN usercode FORMAT a12 HEADING &#8216;SID\/Serial#&#8217; COLUMN os_user FORMAT a10 HEADING &#8216;O\/S User&#8217; COLUMN program FORMAT a25 HEADING &#8216;Program&#8217; COLUMN mode_held FORMAT a15 HEADING &#8216;Mode Held&#8217; COLUMN mode_requested FORMAT a15 HEADING &#8216;Mode Requested&#8217; COLUMN lock_type FORMAT a15 HEADING &#8216;Lock Type&#8217; COLUMN object_name [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":34,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-44","page","type-page","status-publish","hentry"],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/P2NVny-I","_links":{"self":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/44","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=44"}],"version-history":[{"count":4,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/44\/revisions"}],"predecessor-version":[{"id":132,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/44\/revisions\/132"}],"up":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/34"}],"wp:attachment":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=44"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}