{"id":9,"date":"2009-05-14T07:57:07","date_gmt":"2009-05-14T14:57:07","guid":{"rendered":"http:\/\/www.vinodsr.com\/myblog\/?p=9"},"modified":"2009-05-14T08:05:46","modified_gmt":"2009-05-14T15:05:46","slug":"how-to-get-the-locked-tables-list-in-oracle","status":"publish","type":"post","link":"https:\/\/vinodsr.com\/myblog\/2009\/05\/how-to-get-the-locked-tables-list-in-oracle\/","title":{"rendered":"How to Get the Locked Tables List In Oracle"},"content":{"rendered":"

Here is the query to get the locked tables in oracle<\/p>\n

SELECT l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER, SUBSTR(L.SESSION_ID,1,3) SID,
\nS.serial#,
\nSUBSTR(O.OWNER||’.’||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID,
\nDECODE(L.LOCKED_MODE, 0,’NONE’,
\n1,’NULL’,
\n2,’ROW SHARE’,
\n3,’ROW EXCLUSIVE’,
\n4,’SHARE’,
\n5,’SHARE ROW EXCLUSIVE’,
\n6,’EXCLUSIVE’,
\nNULL) LOCK_MODE
\nFROM sys.GV_$LOCKED_OBJECT L, DBA_OBJECTS O, sys.GV_$SESSION S, sys.GV_$PROCESS P
\nWHERE L.OBJECT_ID = O.OBJECT_ID
\nand l.inst_id = s.inst_id
\nAND L.SESSION_ID = S.SID
\nand s.inst_id = p.inst_id
\nAND S.PADDR = P.ADDR(+)
\norder by l.inst_id\u00a0 ;<\/p><\/blockquote>\n

And to get the details of a particular session given by the sid in the above query use this query <\/span><\/p>\n

select STATUS ,\u00a0 PROCESS , PROGRAM , LOGON_TIME\u00a0 from v$session where sid=<SID><\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

Here is the query to get the locked tables in oracle SELECT l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER, SUBSTR(L.SESSION_ID,1,3) SID, S.serial#, SUBSTR(O.OWNER||’.’||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID, DECODE(L.LOCKED_MODE, 0,’NONE’, 1,’NULL’, 2,’ROW…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[45],"class_list":["post-9","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oracle"],"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/vinodsr.com\/myblog\/wp-json\/wp\/v2\/posts\/9","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/vinodsr.com\/myblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vinodsr.com\/myblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vinodsr.com\/myblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/vinodsr.com\/myblog\/wp-json\/wp\/v2\/comments?post=9"}],"version-history":[{"count":5,"href":"https:\/\/vinodsr.com\/myblog\/wp-json\/wp\/v2\/posts\/9\/revisions"}],"predecessor-version":[{"id":24,"href":"https:\/\/vinodsr.com\/myblog\/wp-json\/wp\/v2\/posts\/9\/revisions\/24"}],"wp:attachment":[{"href":"https:\/\/vinodsr.com\/myblog\/wp-json\/wp\/v2\/media?parent=9"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vinodsr.com\/myblog\/wp-json\/wp\/v2\/categories?post=9"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vinodsr.com\/myblog\/wp-json\/wp\/v2\/tags?post=9"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}