Oracle查询锁和kill锁，以及查询所有表

 

--查看被锁的表
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name, s.action,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn Desc;

 

kill锁首先要取得登录Oracle用户的权限，再执行如下数据：

--kill锁
alter system kill session 'sid,SERIAL#'

 

 

--查询Oracle所有表

select Distinct owner from all_tables where owner='UATOLTP';

 

--查询锁表与计算机名匹配

Select a.Owner, a.Object_Name, b.Xidusn, b.Xidslot, b.Xidsqn, b.Session_Id, b.Oracle_Username, b.Os_User_Name, b.Process,
             b.Locked_Mode, c.Machine, c.Status, c.Server, c.Sid, c.Serial#, c.Program
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;

 

注意后面的owner值必须大写，这个我总是忘记