{"id":61,"date":"2008-03-31T13:03:30","date_gmt":"2008-03-31T17:03:30","guid":{"rendered":"http:\/\/www.dev-notes.com\/blog\/2008\/03\/31\/identify-database-locks\/"},"modified":"2008-03-31T13:03:30","modified_gmt":"2008-03-31T17:03:30","slug":"identify-database-locks","status":"publish","type":"post","link":"https:\/\/www.dev-notes.com\/blog\/2008\/03\/31\/identify-database-locks\/","title":{"rendered":"Identify database locks"},"content":{"rendered":"<p>This select statement can be run by someone with DBA privileges (as it makes use of v$ views) to list who is locking what.<\/p>\n<pre class=\"code\">\nselect a.session_id,\n  c.serial#,\n  a.oracle_username,\n  a.os_user_name,\n  c.machine,\n  a.locked_mode,\n  b.object_name,\n  b.object_type\nfrom v$locked_object a,dba_objects b, v$session c\nwhere a.object_id = b.object_id\n  and a.session_id = c.sid;\n<\/pre>\n<p>The query below is more specific.  With it, you will be able to identify exactly which session is block another session, if any such conditions exist.<\/p>\n<pre class=\"code\">\nselect s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' as blocking\nfrom v$lock l1, v$session s1, v$lock l2, v$session s2\nwhere s1.sid=l1.sid and s2.sid=l2.sid\nand l1.block=1 and l2.request > 0\nand l1.id1 = l2.id1 and l2.id2 = l2.id2;\n<\/pre>\n<p>If necessary, we can use the following example to kill a particular session.  It may be useful if the locking session is actually from a hanging process, for example, though typically we should have the user release his\/her lock gracefully.<\/p>\n<pre class=\"code\">\nalter system kill session '123,5555' immediate;\n<\/pre>\n<p>Note that the &#8220;123&#8221; above should be replaced by the session ID, and the &#8220;5555&#8221; should be replaced by the serial #.  Those two values are shown in the first two fields in the select SQL statement above.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This article notes how to quickly identify which user and which session is placing a lock on an object, and how to kill that session, if absolutely necessary.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-61","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/61","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/comments?post=61"}],"version-history":[{"count":0,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/61\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/media?parent=61"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/categories?post=61"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/tags?post=61"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}