解析oracle对select加锁的方法以及锁的查询

解析oracle对select加锁的方法以及锁的查询
一、oracle对select加锁方法

create table test(a number,b number);
insert into test values(1,2);
insert into test values(3,4);
insert into test values(8,9);
commit;
---session 1 模拟选中一个号码
sql> select * from test where a =1 for update skip locked;
         a          b
---------- ----------
         1          2
---session 2 对a=1再进行select
sql> select * from test where a = 1 for update skip locked;
未选定行
-- session 3 全表select
sql> select * from test for update skip locked;
         a          b
---------- ----------
         3          4
         8          9
sql>

二、查询那些用户，操纵了那些表造成了锁机

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=(上面查出来的会话id)

五、
１.查哪个过程被锁
查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;