ORACLE查询锁表与解锁

• 查询语句

SELECT
    t2.username,
    t2.sid,
    t2.serial#,
    t3.object_name,
    t2.OSUSER,
    t2.MACHINE,
    t2.PROGRAM,
    t2.LOGON_TIME,
    t2.COMMAND,
    t2.LOCKWAIT,
    t2.SADDR,
    t2.PADDR,
    t2.TADDR,
    t2.SQL_ADDRESS,
    t1.LOCKED_MODE 
FROM
    v$locked_object t1,
    v$session t2,
    dba_objects t3 
WHERE
    t1.session_id = t2.sid 
    AND t1.object_id = t3.object_id 
ORDER BY
    t2.logon_time;

• 删除锁表的进程(sid和seial#就是查询出来的进程号和序列号)

alter system kill session 'sid,seial#';

或者

SELECT 'alter system kill session '''||SID || ',' || SERIAL#||''';'
  FROM V$SESSION
 WHERE SID in (
    SELECT SESSION_ID
    FROM V$LOCKED_OBJECT, USER_OBJECTS
  WHERE V$LOCKED_OBJECT.OBJECT_ID = USER_OBJECTS.OBJECT_ID
);