導(dǎo)讀:1建站知識本篇文章是對oracle對select加鎖的方法以及鎖的查詢進行了詳細的分析介紹，需要的朋友參考下企業(yè)網(wǎng)站建設(shè)網(wǎng)站seo優(yōu)化軟件。

解析oracle對select加鎖的方法以及鎖的查詢一、oracle對select加鎖方法

復(fù)制代碼 代碼如下:

create table test(a number,b number);insert into test values(1,2);insert into test values(3,4);insert into test valuesseo網(wǎng)站關(guān)鍵詞優(yōu)化(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>

二、查詢那些用戶，操縱了那些表造成了鎖機

復(fù)制代碼 代碼如下:

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網(wǎng)站優(yōu)化ct_id=b.object_id四、 查出對應(yīng)的SQL語句

復(fù)制代碼 代碼如下:

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).再殺操作系統(tǒng)進程:KILL -9 剛才查出的SPID或ORAKILL 剛才查出的SID 剛才查出的SPID六、查找最耗費系統(tǒng)資源的SQL

復(fù)制代碼 代碼如下:

--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

復(fù)制代碼 代碼如下:

--IOsele網(wǎng)站建設(shè)哪家好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

關(guān)鍵詞標簽: 標簽 加鎖