查询锁表记录

--查询锁表
SELECT P.SPID,
       A.SID,
       A.SERIAL#,
       C.OBJECT_NAME,
       B.SESSION_ID,
       B.ORACLE_USERNAME,
       B.OS_USER_NAME
  FROM V$PROCESS P, V$SESSION A, V$LOCKED_OBJECT B, ALL_OBJECTS C
 WHERE P.ADDR = A.PADDR
   AND A.PROCESS = B.PROCESS
   AND C.OBJECT_ID = B.OBJECT_ID;

alter system kill session '1525,49455' immediate;

--1.查找用户owner下的被锁定的存储过程
select * from V$DB_OBJECT_CACHE where owner='CTPROD' AND LOCKS!='0';
--2.通过查出来的存储过程名查找sid
select sid,sql_text from v$open_cursor where UPPER(sql_text) like '%CALL_PROP_CONTRACT_CAL%';
--3.通过sid查找serial#
select sid,serial#,paddr from v$session where sid='582';
--4.杀死会话参数为查出来的sid和serial#
alter system kill session '582,1744';
--immediate
---正在执行的自动任务
select * from ggautotask t  where t.status ='R';

--锁表查询SQL：
SELECT object_name, machine, s.sid, s.serial#, s.OSUSER
  FROM gv$locked_object l, dba_objects o, gv$session s
 WHERE l.object_id　 = o.object_id
   AND l.session_id = s.sid;
--杀会话
ALTER system kill session 'sidxxx, serialxxx';

--杀不掉的进程用这个加个immediate
ALTER SYSTEM KILL SESSION '476, 46175' immediate;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30488948/viewspace-2137577/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30488948/viewspace-2137577/