/**locked*query locked object and analyse reason,kill it**/select 'alter system kill session ''' || SID || ',' || SERIAL# || ''';'  from (select distinct a.sid,                        a.Serial#,                        status,                        machine,                        LOCKWAIT,                        logon_time          from v$session a, v$locked_object b         where (a.status = 'ACTIVE' or a.status = 'INACTIVE')           and a.sid = b.session_id           and b.ORACLE_USERNAME = 'XYHISTEST' --加上用户名可过滤用户资源        )       /*2、批量执行第一步生成的语句alter system kill session 'sid,serial#';alter system kill session '6976,33967';*/   /*3、查询oracle用户名，机器名，锁表对象*/SELECT l.session_id sid,       s.serial#,       l.locked_mode,       l.oracle_username,       l.os_user_name,       s.machine,       s.terminal,       o.object_name,       s.logon_time  FROM v$locked_object l, all_objects o, v$session s WHERE l.object_id = o.object_id   AND l.session_id = s.sid ORDER BY sid, s.serial#;/*3.或者*/select s.SID, s.OSUSER, p.spid as OSPID, s.MACHINE, s.TERMINAL, s.PROGRAM  from v$session s, v$process p where s.sid = 6 --session_id   and s.paddr = p.addr;/*4、查询是执行何sql语句导致锁表的*/select b.sql_text  from v$session a, v$sql b where a.sid = 6 --session_id   and a.SQL_ADDRESS = b.ADDRESS(+);/*5、查询是执行何sql语句导致锁表的*/SELECT l.session_id sid,       s.serial#,       l.locked_mode,       l.oracle_username,       s.user#,       l.os_user_name,       s.machine,       s.terminal,       a.sql_text,       a.action  FROM v$sqlarea a, v$session s, v$locked_object l WHERE l.session_id = s.sid   AND s.prev_sql_addr = a.address ORDER BY sid, s.serial#;

oracle 查看锁表情况并处理锁表