导读:1建站知识本篇文章是对oracle对select加锁的方法以及锁的查询进行了详细的分析介绍，需要的朋友参考下企业网站建设网站seo优化软件。

解析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 valuesseo网站关键词优化(8,9);commit;---session 1 模拟选中一个号码SQL> select * from test where a =1 for update skip locked; A B---------- ---------- 1 2---session 2 对a=1再进行selectSQL> select * from test where a = 1 for update skip locked;未选定行-- session 3 全表selectSQL> select * from test for update skip locked; A B---------- ---------- 3 4 8 9SQL>

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

复制代码 代码如下:

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.osuserFROM v$session s,v$lock l,all_objects oWHERE l.sid = s.sidAND l.id1 = o.object_id(+)AND s.username is NOT Null

三、查出被锁的表，和锁住这个表的会话IDselect a.session_id ,b.* from v$locked_object a,all_objects bwhere a.obje百度seo网站优化ct_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_READSfrom v$sql vs,v$session vsesswhere vs.ADDRESS=vsess.SQL_ADDRESSand 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;

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

复制代码 代码如下:

--CPUselect b.sql_text,a.buffer_gets,a.executions,a.buffer_gets/decode(a.executions , 0 , 1 , a.executions),c.usernamefrom V$sqlarea a,v$sqltext_with_newlines b,dba_users cwhere a.parsing_user_id = c.user_idand a.address = b.addressorder by a.buffer_gets desc , b.piece

复制代码 代码如下:

--IOsele网站建设哪家好ct b.sql_text,a.disk_reads,a.executions,a.disk_reads/decode(a.executions , 0 , 1 , a.executions),c.usernamefrom v$sqlarea a,v$sqltext_with_newlines b,dba_users cwhere a.parsing_user_id = c.user_idand a.address = b.addressorder by a.disk_reads desc , b.piece

关键词标签: 标签 加锁