{"id":446,"date":"2014-06-03T10:46:39","date_gmt":"2014-06-03T08:46:39","guid":{"rendered":"http:\/\/www.rocworks.at\/wordpress\/?p=446"},"modified":"2014-08-04T11:49:56","modified_gmt":"2014-08-04T09:49:56","slug":"oracle-query-session-locks-and-objects","status":"publish","type":"post","link":"https:\/\/www.rocworks.at\/wordpress\/?p=446","title":{"rendered":"Oracle query session locks and objects…."},"content":{"rendered":"

SELECT
\n s.inst_id,
\n p.spid process_id,
\n a.oracle_username,
\n a.os_user_name,
\n a.locked_mode,
\n b.object_name,
\n b.object_type,
\n s.sid,
\n s.serial#,
\n s.program
\nfrom
\n gv$locked_object a, dba_objects b, gv$session s, gv$process p
\nwhere
\n a.object_id = b.object_id AND
\n a.process = s.process AND
\n a.SESSION_ID = s.SID AND
\n p.addr = s.paddr AND
\n p.inst_id = s.inst_id
\n<\/code><\/p>\n

In a cluster database use gv$ instead of v$ !<\/p>\n


\nalter system [disconnect|kill] session 'sid,serial#,@inst_id' immediate;
\n<\/code><\/p>\n

kill locked sessions
\nSELECT 'alter system kill session '''||b.sid||','||b.SERIAL#||''';'
\nFROM v$lock a, v$session b, dba_objects o1, dba_objects o2
\nWHERE a.SID = b.SID
\nAND a.id1 = o1.object_id (+)
\nAND a.id2 = o2.object_id (+)
\nAND b.blocking_session IS NOT NULL
\nAND username IS NOT NULL<\/code><\/p>\n

query blocking sessions:
\n=======================================================
\nselect l1.sid, ' IS BLOCKING ', l2.sid
\nfrom v$lock l1, v$lock l2
\nwhere l1.block =1 and l2.request > 0
\nand l1.id1=l2.id1
\nand l1.id2=l2.id2<\/code><\/p>\n

=======================================================
\nselect s1.username || '@' || s1.machine
\n|| ' ( SID=' || s1.sid || ' ) is blocking '
\n|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
\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
\nand l1.id2 = l2.id2 ;<\/code><\/p>\n

=======================================================
\nselect do.object_name,
\n row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
\n dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
\nfrom v$session s, dba_objects do
\nwhere sid=543
\nand s.ROW_WAIT_OBJ# = do.OBJECT_ID ;<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"

SELECT s.inst_id, p.spid process_id, a.oracle_username, a.os_user_name, a.locked_mode, b.object_name, b.object_type, s.sid, s.serial#, s.program from gv$locked_object a, dba_objects b, gv$session s, gv$process p where a.object_id = b.object_id AND a.process = s.process AND a.SESSION_ID = s.SID AND p.addr = s.paddr AND p.inst_id = s.inst_id In a cluster database use gv$ instead of v$ ! alter system [disconnect|kill] session … Continue reading Oracle query session locks and objects….<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-446","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/446","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=446"}],"version-history":[{"count":10,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/446\/revisions"}],"predecessor-version":[{"id":519,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/446\/revisions\/519"}],"wp:attachment":[{"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=446"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=446"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=446"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}