Table Lock

>> 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#'