查詢鎖表記錄

--查詢鎖表
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;