日期:2014-05-16  浏览次数:20579 次

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值必须大写,这个我总是忘记