oracle 锁表处理

说明一下，我没能力写sql，都是百度出来的，仅仅做过优化适合我使用而已

查看所有的锁(活动+非活动的)

set pagesize 199 linesize 3000;
col OWNER for a14;
col ORACLE_USERNAME for a10;
col MACHINE for a30;
col OBJECT_NAME for a30;
col OS_USER_NAME for a20;
col  PROGRAM for a20;
SELECT B.ORACLE_USERNAME,A.OBJECT_NAME, B.OS_USER_NAME, B.LOCKED_MODE, C.MACHINE,C.PROGRAM,C.STATUS FROM ALL_OBJECTS A,V$LOCKED_OBJECT B,SYS.GV_$SESSION C
WHERE ( A.OBJECT_ID = B.OBJECT_ID ) AND (B.PROCESS = C.PROCESS )  ORDER BY 1,2;

select LOCK_INFO.OWNER || '.' || LOCK_INFO.OBJ_NAME as "已锁对象名称", --对象名称(已经被锁住)
LOCK_INFO.SUBOBJ_NAME as "已锁子对象名称", -- 子对象名称(已经被锁住)
SESS_INFO.MACHINE as "机器名称", -- 机器名称
LOCK_INFO.SESSION_ID as "会话ID", -- 会话SESSION_ID
SESS_INFO.SERIAL# as "会话SERIAL#", -- 会话SERIAL#
SESS_INFO.SPID as "OS系统的SPID", -- OS系统的SPID
(SELECT INSTANCE_NAME FROM V$INSTANCE) "实例名SID", --实例名SID
LOCK_INFO.ORA_USERNAME as "ORACLE用户", -- ORACLE系统用户名称
LOCK_INFO.OS_USERNAME as "OS用户", -- 操作系统用户名称
LOCK_INFO.PROCESS as "进程编号", -- 进程编号
LOCK_INFO.OBJ_ID as "对象ID", -- 对象ID
LOCK_INFO.OBJ_TYPE as "对象类型", -- 对象类型
SESS_INFO.LOGON_TIME as "登录时间", -- 登录时间
SESS_INFO.PROGRAM as "程序名称", -- 程序名称
SESS_INFO.STATUS as "会话状态", -- 会话状态
SESS_INFO.LOCKWAIT as "等待锁", -- 等待锁
SESS_INFO.ACTION as "动作", -- 动作
SESS_INFO.CLIENT_INFO as "客户信息" -- 客户信息
from (select obj.OWNER as OWNER,
obj.OBJECT_NAME as OBJ_NAME,
obj.SUBOBJECT_NAME as SUBOBJ_NAME,
obj.OBJECT_ID as OBJ_ID,
obj.OBJECT_TYPE as OBJ_TYPE,
lock_obj.SESSION_ID as SESSION_ID,
lock_obj.ORACLE_USERNAME as ORA_USERNAME,
lock_obj.OS_USER_NAME as OS_USERNAME,
lock_obj.PROCESS as PROCESS
from (select * from all_objects
where object_id in (select object_id from v$locked_object)) obj,v$locked_object lock_obj
where obj.object_id = lock_obj.object_id) LOCK_INFO,
(select SID,SERIAL#,LOCKWAIT,STATUS,(select spid from v$process where addr = a.paddr) spid,PROGRAM,ACTION,CLIENT_INFO,LOGON_TIME,MACHINE
from v$session a) SESS_INFO  order by LOCK_INFO.SESSION_ID;

只查ACTIVE状态的锁表

set pagesize 199 linesize 3000;
col OWNER for a14;
col ORACLE_USERNAME for a10;
col MACHINE for a30;
col OBJECT_NAME for a30;
col OS_USER_NAME for a20;
col  PROGRAM for a20;
SELECT B.ORACLE_USERNAME,A.OBJECT_NAME, B.OS_USER_NAME, B.LOCKED_MODE, C.MACHINE,C.PROGRAM,C.STATUS FROM ALL_OBJECTS A,V$LOCKED_OBJECT B,SYS.GV_$SESSION C
WHERE ( A.OBJECT_ID = B.OBJECT_ID ) AND (B.PROCESS = C.PROCESS ) and C.STATUS = 'ACTIVE' ORDER BY 1,2;

set pagesize 199 linesize 199;
col ORACLE_USERNAME for a20;
col "OS用户" for a20;
col "已锁对象名称" for a30;
select LOCK_INFO.OWNER || '.' || LOCK_INFO.OBJ_NAME as "已锁对象名称", --对象名称(已经被锁住)
LOCK_INFO.SUBOBJ_NAME as "已锁子对象名称", -- 子对象名称(已经被锁住)
SESS_INFO.MACHINE as "机器名称", -- 机器名称
LOCK_INFO.SESSION_ID as "会话ID", -- 会话SESSION_ID
SESS_INFO.SERIAL# as "会话SERIAL#", -- 会话SERIAL#
SESS_INFO.SPID as "OS系统的SPID", -- OS系统的SPID
(SELECT INSTANCE_NAME FROM V$INSTANCE) "实例名SID", --实例名SID
LOCK_INFO.ORA_USERNAME as "ORACLE用户", -- ORACLE系统用户名称
LOCK_INFO.OS_USERNAME as "OS用户", -- 操作系统用户名称
LOCK_INFO.PROCESS as "进程编号", -- 进程编号
LOCK_INFO.OBJ_ID as "对象ID", -- 对象ID
LOCK_INFO.OBJ_TYPE as "对象类型", -- 对象类型
SESS_INFO.LOGON_TIME as "登录时间", -- 登录时间
SESS_INFO.PROGRAM as "程序名称", -- 程序名称
SESS_INFO.STATUS as "会话状态", -- 会话状态
SESS_INFO.LOCKWAIT as "等待锁", -- 等待锁
SESS_INFO.ACTION as "动作", -- 动作
SESS_INFO.CLIENT_INFO as "客户信息" -- 客户信息
from (select obj.OWNER as OWNER,
obj.OBJECT_NAME as OBJ_NAME,
obj.SUBOBJECT_NAME as SUBOBJ_NAME,
obj.OBJECT_ID as OBJ_ID,
obj.OBJECT_TYPE as OBJ_TYPE,
lock_obj.SESSION_ID as SESSION_ID,
lock_obj.ORACLE_USERNAME as ORA_USERNAME,
lock_obj.OS_USER_NAME as OS_USERNAME,
lock_obj.PROCESS as PROCESS
from (select * from all_objects
where object_id in (select object_id from v$locked_object)) obj,
v$locked_object lock_obj
where obj.object_id = lock_obj.object_id) LOCK_INFO,
(select SID,SERIAL#,LOCKWAIT,STATUS,(select spid from v$process where addr = a.paddr) spid,PROGRAM,ACTION,CLIENT_INFO,LOGON_TIME,MACHINE
from v$session a) SESS_INFO where LOCK_INFO.SESSION_ID = SESS_INFO.SID order by LOCK_INFO.SESSION_ID;

锁表直接得出kill命令如下：
set pagesize 9999 linesize 199;
SELECT 'alter system kill session ' ||''''||C.SID||','||C.SERIAL#||''''||' immediate;' FROM ALL_OBJECTS A,V$LOCKED_OBJECT B,SYS.GV_$SESSION C
WHERE ( A.OBJECT_ID = B.OBJECT_ID ) AND (B.PROCESS = C.PROCESS ) ORDER BY 1;