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.OSUSER
AS
OS_USER,
VS.MACHINE,
VS.STATUS,
VS.PROGRAM,
VS.LOGON_TIME
AS
"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"
FROM
DBA_DDL_LOCKS DDL, V$SESSION VS, V$PROCESS VP
WHERE
DDL.SESSION_ID = VS.SID
AND
VS.PADDR = VP.ADDR;
|