>> TABLE LOCK
select * from dba_waiters;
select * from dba_blockers;
SELECT P.SPID, S.SID, S.SERIAL# FROM V$PROCESS P, V$SESSION S;
SELECT P.SPID, S.SID, S.SERIAL# FROM V$PROCESS P, V$SESSION S where SID='< >';
ALTER SYSTEM KILL SESSION 'SID,SERIAL#';
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type FROM V$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request;
++++++++++++++++++++++++++++++++++++++++++++++++
>> Table Lock
1) Run the Following Script to Get Locked Tables in Session
BEGIN
DBMS_OUTPUT.enable (1000000);
FOR do_loop IN (SELECT session_id,
a.object_id,
xidsqn,
oracle_username,
b.owner owner,
b.object_name object_name,
b.object_type object_type
FROM v$locked_object a, dba_objects b
WHERE xidsqn != 0 AND b.object_id = a.object_id)
LOOP
DBMS_OUTPUT.put_line ('.');
DBMS_OUTPUT.put_line ('Blocking Session : ' || do_loop.session_id);
DBMS_OUTPUT.
put_line (
'Object (Owner/Name): '
|| do_loop.owner
|| '.'
|| do_loop.object_name);
DBMS_OUTPUT.put_line ('Object Type : ' || do_loop.object_type);
FOR next_loop
IN (SELECT sid
FROM v$lock
WHERE id2 = do_loop.xidsqn AND sid != do_loop.session_id)
LOOP
DBMS_OUTPUT.put_line ('Sessions being blocked : ' || next_loop.sid);
END LOOP;
END LOOP;
END;
2) Get SID & Serial Number to Kill Session
SQL> select * from v$session where sid in 'SID,SERIAL#'
SQL> alter system kill session 'SID,SERIAL#'
