锁检查脚本

--用dba用户执行以下语句，可以查看到被锁的语句。
select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object WHERE object_id='107181'))

--查找锁的进程：
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS,D.object_name
FROM V$LOCKED_OBJECT l,V$SESSION S,DBA_OBJECTS D WHERE l.SESSION_ID=S.SID AND D.object_id=L.OBJECT_ID;

GPS_PARK_RECORD

SELECT l.SESSION_ID
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;
--查询锁对应的os进程
select pro.spid from v$session ses,v$process pro where ses.sid=439 and ses.paddr=pro.addr;

--kill掉这个锁的进程：

alter system kill session '286,24060';
SELECT 'alter system kill session '''|| S.SID||','||s.SERIAL#||''';'
FROM V$LOCKED_OBJECT l,V$SESSION S,dba_objects o

WHERE l.SESSION_ID=S.SID and o.OBJECT_ID=l.OBJECT_ID and o.OBJECT_NAME='GPS_PARK_RECORD';

--kill相关链接此对此对象session
select /*+rule */ 'kill -9 '||p.SPID from gv$access a ,gv$session s ,gv$process p
where a.INST_ID=2
 and  a.SID=s.SID
 and  s.PADDR=p.ADDR
 and  a.OBJECT='PKG_EXP_OP_TASK'
 and  a.OWNER='EXP' ;