查询oracle被锁对象并解锁

最新推荐文章于 2024-07-10 09:14:05 发布

转载最新推荐文章于 2024-07-10 09:14:05 发布 · 2.9k 阅读

Oracle Basic 专栏收录该内容

23 篇文章

订阅专栏

转：http://www.xifenfei.com/607.html

1、查询oracle被锁对象及其语句

SELECT 
a_s.owner,
  a_s.object_name,
  a_s.object_type,
  VN.SID,
  VN.SERIAL#,
  VS.SPID"OS_PID",
  VN.PROCESS"CLIENT_PID",
  VN.USERNAME,
  VN.OSUSER,
  VN.MACHINE"HOSTNAME"
,
  VN.TERMINAL,
  VN.PROGRAM,
  TO_CHAR(VN.LOGON_TIME,'YYYY-MM-DD HH24:MI:SS')"LOGIN_TIME",
  'alter system kill session '''||vn.sid||','||vn.serial#||''';'"ORACKE_KILL",
  'kill -9 '|| VS.SPID"OS_KILL"
FROM 
ALL_OBJECTS A_S,
  V$LOCKED_OBJECT V_T,
  V$SESSION VN,
  V$PROCESS VS
WHERE 
A_S.OBJECT_ID=V_T.OBJECT_ID
AND 
V_T.SESSION_ID =VN.SID
AND 
VS.ADDR=VN.PADDR
AND 
VN.USERNAME NOT IN('SYSMAN','SYS');

2、查询该sid的sql语句

select 
* from v$sql vl,v$session vn
where 
vl.ADDRESS= decode(vn.SQL_ADDRESS,null,vn.PREV_SQL_ADDR,VN.SQL_ADDRESS)
and 
vn.sid=&sid;

3、解锁

alter 
system kill session 'sid,serial#';
--note:不能kill自身

4、查询被锁对象增强版

SELECT 
DDL.OWNER AS 用户,
       DDL.NAME AS
对象,
       DDL.type AS
类型,
       VS.OSUSERAS
OS_USER,
       VS.MACHINE,
       VS.STATUS,
       VS.PROGRAM,
       VS.LOGON_TIMEAS
"LOGIN_TIME",
       VP.SPID,
       'kill -9 '|| VP.SPID
AS OS_KILL,
       vs.sid,
       vs.SERIAL#,
       'alter system kill session '''|| vs.sid ||
',' || vs.serial# ||
       ''';'"ORACKE_KILL"
  FROMDBA_DDL_LOCKS DDL, V$SESSION VS, V$PROCESS VP
 WHEREDDL.SESSION_ID = VS.SID
   ANDVS.PADDR = VP.ADDR;