Get the locked tables

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 SHARE’, 3,’ROW EXCLUSIVE’, 4,’SHARE’, 5,’SHARE ROW EXCLUSIVE’, 6,’EXCLUSIVE’, NULL) LOCK_MODE
FROM
sys.GV_$LOCKED_OBJECT L
, DBA_OBJECTS O
, sys.GV_$SESSION S
, sys.GV_$PROCESS P
WHERE
L.OBJECT_ID = O.OBJECT_ID
AND l.inst_id = s.inst_id
AND L.SESSION_ID = S.SID
AND s.inst_id = p.inst_id
AND S.PADDR = P.ADDR(+)
ORDER BY
l.inst_id
;

And to get the details of a particular session given by the sid in the above query use this query


SELECT STATUS ,PROCESS, PROGRAM, LOGON_TIME FROM v$session WHERE sid=

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s