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; ?
