Table Locks

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  ;

Or you can do the below :

-------------------------

select * From v$locked_object;

select * From v$session where SID = '<>';

select * from dba_objects where object_id = '<>';

Locked objects :

----------------

select object_name, owner, object_type from dba_objects where object_id in (select object_id from v$locked_object);

KILL SESSION COMMAND

--------------------

ALTER SYSTEM KILL SESSION 'SID,Serial#' IMMEDIATE;

To find locks on table

----------------------

-- Check if the session has a locked transaction.

SELECT username, sid, serial# FROM v$session WHERE sid IN (SELECT session_id FROM v$locked_object)

/

-- Query v$lock table from another session and see the different lock modes

select sid, type, lmode, request, block from v$lock where sid in (select session_id from v$locked_object where object_id=xxx);

select * from v$locked_object

where OBJECT_ID=(select OBJECT_ID from dba_objects where table_name=<table_name>

and OBJECT_TYPE='TABLE')

select session_id from v$locked_object where object_id=(select object_id from object_type='TABLE' and owner=' ');

-- Kill the insignificant session.

ALTER SYSTEM KILL SESSION '&SID,&SERIAL'

/