oracle不加锁查询吗,解析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

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. 杀进程ALTER SYSTEM KILL SESSION '查出的SID,查出的SERIAL#';

KILL -9 刚才查出的SPID

或

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

sql

代码如下:

--cpu

select b.sql_text,

a.buffer_gets,

a.executions,

a.buffer_gets/decode(a.executions,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.disk_reads/decode(a.executions,

c.username

from v$sqlarea a,

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;

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。