一、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
 
二、查询那些用户，操纵了那些表造成了锁机 
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;