ORACLE 查询数据库锁相关的语句

--查询那些用户，操纵了那些表造成了锁机 
SELECT s.username,
       decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
       o.owner,
       o.object_name,
       o.object_type,
       s.sid,
       s.serial#,
       s.terminal,
       s.machine,
       s.program,
       s.osuser
FROM v$session s, v$lock l, all_objects o
WHERE l.sid = s.sid
   AND l.id1 = o.object_id(+)
   AND s.username is NOT Null;

--查出被锁的表，和锁住这个表的会话ID 
select a.session_id, b.*
from v$locked_object a, all_objects b
where a.object_id = b.object_id;

--查出对应的SQL语句 
select vs.SQL_TEXT,
       vsess.sid,
       vsess.SERIAL#,
       vsess.MACHINE,
       vsess.OSUSER,
       vsess.TERMINAL,
       vsess.PROGRAM,
       vs.CPU_TIME,
       vs.DISK_READS
from v$sql vs, v$session vsess
where vs.ADDRESS = vsess.SQL_ADDRESS
   and vsess.sid = (1033);

五、

１.查哪个过程被锁 

查V$DB_OBJECT_CACHE视图: 

SELECT * FROM V$DB_OBJECT_CACHE WHERE OWNER='过程的所属用户' AND LOCKS!='0'; 


2. 查是哪一个SID,通过SID可知道是哪个SESSION. 

查V$ACCESS视图: 

SELECT * FROM V$ACCESS WHERE OWNER='过程的所属用户' AND NAME='刚才查到的过程名'; 


3. 查出SID和SERIAL# 

查V$SESSION视图: 

SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID='刚才查到的SID' 


查V$PROCESS视图: 

SELECT SPID FROM V$PROCESS WHERE ADDR='刚才查到的PADDR'; 


4. 杀进程 

(1).先杀ORACLE进程: 

ALTER SYSTEM KILL SESSION '查出的SID,查出的SERIAL#'; 


(2).再杀操作系统进程: 

KILL -9 刚才查出的SPID 

或 

ORAKILL 刚才查出的SID 刚才查出的SPID

六、查找最耗费系统资源的SQL 
--CPU 
select b.sql_text, 
a.buffer_gets, 
a.executions, 
a.buffer_gets/decode(a.executions , 0 , 1 , a.executions), 
c.username 
from V$sqlarea a, 
v$sqltext_with_newlines b, 
dba_users c 
where a.parsing_user_id = c.user_id 
and a.address = b.address 
order by a.buffer_gets desc , b.piece 
; 

--IO 
select b.sql_text, 
a.disk_reads, 
a.executions, 
a.disk_reads/decode(a.executions , 0 , 1 , a.executions), 
c.username 
from v$sqlarea a, 
v$sqltext_with_newlines b, 
dba_users c 
where a.parsing_user_id = c.user_id 
and a.address = b.address 
order by a.disk_reads desc , b.piece 
; 


select s.sid,s.value "CPU Used" 
from v$sesstat s,v$statname n 
where s.statistic#=n.statistic# and n.name='CPU used by this session' 
and s.value>0 
order by 2 desc;