xml地图|网站地图|网站标签 [设为首页] [加入收藏]

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

时间:2020-05-07 05:30来源:计算机
解析oracle对select加锁的方法以及锁的查询 一、oracle对select加锁方法 复制代码 代码如下:create table test(a number,bnumber);insert into test values(1,2);insert into test values(3,4);insertinto test values(8,9);com

解析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再进行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.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_READSfrom v$sql vs,v$session vsesswhere vs.ADDRESS=vsess.SQL_ADDRESSand vsess.sid=(上面查出来的会话ID)五、1.查哪个过程被锁查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复制代码 代码如下:--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复制代码 代码如下:--IOselect 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复制代码 代码如下:select s.sid,s.value "CPU Used"from v$sesstat s,v$statname nwhere s.statistic#=n.statistic# and n.name='CPU used by this session'and s.value0order by 2 desc;

编辑:计算机 本文来源:解析oracle对select加锁的方法以及锁的查询

关键词: