eMarcel.com

CREATE TABLE MYPHONEBOOK
(
"FIRST_NAME"     VARCHAR2(20 CHAR),
"LAST_NAME"      VARCHAR2(20 CHAR),
"PHONE_NUMBER"   VARCHAR2(20 CHAR)
)
;

INSERT INTO MYPHONEBOOK (FIRST_NAME, LAST_NAME, PHONE_NUMBER) VALUES ( 'Donald', 'OConnell', '650.507.9833');
INSERT INTO MYPHONEBOOK (FIRST_NAME, LAST_NAME, PHONE_NUMBER) VALUES ( 'Douglas', 'Grant', '650.507.9844');
INSERT INTO MYPHONEBOOK (FIRST_NAME, LAST_NAME, PHONE_NUMBER) VALUES ( 'Jennifer', 'Whalen', '515.123.4444');
...
...
...

  SELECT oracle_username || ' (' || s.osuser || ')' username,
         s.sid || ',' || s.serial# sess_id,
         owner || '.' || object_name object,
         object_type,
         DECODE (l.block, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') STATUS,
         DECODE (v.locked_mode,
                 0, 'None',
                 1, 'Null',
                 2, 'Row-S (SS)',
                 3, 'Row-X (SX)',
                 4, 'Share',
                 5, 'S/Row-X (SSX)',
                 6, 'Exclusive',
                 TO_CHAR (lmode))
            mode_held
    FROM v$locked_object v,
         dba_objects d,
         v$lock l,
         v$session s
   WHERE     v.object_id = d.object_id
         AND v.object_id = l.id1
         AND v.session_id = s.sid
ORDER BY oracle_username, session_id;

SELECT c.owner,
  c.object_name,
  c.object_type,
  b.sid,
  b.serial#,
  b.STATUS,
  b.osuser,
  b.machine
FROM v$locked_object a ,
  v$session b,
  dba_objects c
WHERE b.sid     = a.session_id
AND a.object_id = c.object_id;

OWNER	 OBJECT_NAME	SID,SERIAL OBJECT      OBJECT_TYPE   STATUS	     MODE_HELD
SCHEMA_1 (user.test)	738,9076   SCHEMA1.EMP TABLE	     NOT    Blocking Share

SQL> ALTER SYSTEM KILL SESSION '579, 703' IMMEDIATE;

SQL> exec dbms_xdb.setListenerLocalAccess (l_access => FALSE);

SQL> exec dbms_xdb.setListenerLocalAccess (l_access => TRUE);