DBA的高級(jí)查詢語句（精華）

1、DBA的高級(jí)查詢語句（精華）目錄查看表空間的名稱及大小:2查看表空間物理文件的名稱及大小:2查看回滾段名稱及大小:2如何查看某個(gè)回滾段里面，跑的什么事物或者正在執(zhí)行什么sql語句:3查看控制文件:3查看日志文件:3如何查看當(dāng)前SQL*PLUS用戶的sid和serial#:3如何查看當(dāng)前數(shù)據(jù)庫的字符集:3怎么判斷當(dāng)前正在使用何種SQL優(yōu)化方式:3如何查看系統(tǒng)當(dāng)前最新的SCN號(hào)：3在ORACLE中查找TRACE文件的腳本:3如何查看客戶端登陸的IP地址:3如何在生產(chǎn)數(shù)據(jù)庫中創(chuàng)建一個(gè)追蹤客戶端IP地址的觸發(fā)器：3REM 記錄登陸信息的觸發(fā)器4查詢當(dāng)前日期:4查看所有表空間對(duì)應(yīng)的數(shù)據(jù)文件名：4查看表空

2、間的使用情況:4查看數(shù)據(jù)文件的hwm（可以resize的最小空間）和文件頭大小:4數(shù)據(jù)文件大小及頭大小:4數(shù)據(jù)庫各個(gè)表空間增長情況的檢查：5數(shù)據(jù)庫對(duì)象下一擴(kuò)展與表空間的free擴(kuò)展值的檢查：5Disk Read最高的SQL語句的獲?。?查找前十條性能差的sql6等待時(shí)間最多的5個(gè)系統(tǒng)等待事件的獲取：6查看當(dāng)前等待事件的會(huì)話:6找到與所連接的會(huì)話有關(guān)的當(dāng)前等待事件：6Oracle所有回滾段狀態(tài)的檢查：6Oracle回滾段擴(kuò)展信息的檢查:6查看回滾段的使用情況，哪個(gè)用戶正在使用回滾段的資源:7如何查看一下某個(gè)shared_server正在忙什么:7數(shù)據(jù)庫共享池性能檢查:7檢查數(shù)據(jù)重載比率:7檢查數(shù)

3、據(jù)字典的命中率:7檢查共享內(nèi)存的剩余情況:7數(shù)據(jù)高速緩沖區(qū)性能檢查:7檢查buffer pool HIT_RATIO執(zhí)行7數(shù)據(jù)庫回滾段性能檢查:8檢查Ratio執(zhí)行8檢查count/value執(zhí)行:8檢查average_wait執(zhí)行:8檢查RBS header get ratio執(zhí)行:8殺會(huì)話的腳本:8查看排序段的性能:8查看數(shù)據(jù)庫庫對(duì)象:8查看數(shù)據(jù)庫的版本:8查看數(shù)據(jù)庫的創(chuàng)建日期和歸檔方式:8捕捉運(yùn)行很久的SQL:9查看數(shù)據(jù)表的參數(shù)信息:9查看還沒提交的事務(wù):9查找object為哪些進(jìn)程所用:9查看回滾段:9耗資源的進(jìn)程(top session):9根據(jù)PID查找相應(yīng)的語句:10根據(jù)SID找

4、ORACLE的某個(gè)進(jìn)程:10如何查看數(shù)據(jù)庫中某用戶，正在運(yùn)行什么SQL語句10如何查出前臺(tái)正在發(fā)出的sql語句:10查詢當(dāng)前所執(zhí)行的SQL語句：10找出消耗CPU最高的進(jìn)程對(duì)應(yīng)的SQL語句：10CPU用率最高的2條SQL語句的獲取11查看鎖（lock）情況:11查看等待（wait）情況:12查看sga情況:12查看catched object:13查看V$SQLAREA:13查看object分類數(shù)量:13有關(guān)connection的相關(guān)信息:13查詢表空間使用情況:14查看表空間的名稱及大小:SQL>select t.tablespace_name, round(sum(bytes/(10

5、24*1024),0) ts_sizefrom dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;查看表空間物理文件的名稱及大小:SQL>select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;查看回滾段名稱及大小:SQL>s

6、elect segment_name, tablespace_name, r.status, (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent, max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat vWhere r.segment_id = v.usn(+) order by segment_name;如何查看某個(gè)回滾段里面，跑的什么事物或者正在執(zhí)行什么sql語句:SQL>select d.sql_text,

7、from v$rollname a,v$transaction b,v$session c,v$sqltext d where a.usn=b.xidusn and b.addr=c.taddr and c.sql_address= d.address and c.sql_hash_value=d.hash_value and a.usn=1;(備注：你要看哪個(gè)，就把usn=?寫成幾就行了)查看控制文件:SQL>select * from v$controlfile;查看日志文件:SQL> col member format a50SQL>select * from v$lo

8、gfile;如何查看當(dāng)前SQL*PLUS用戶的sid和serial#:SQL>select sid, serial#, status from v$session where audsid=userenv('sessionid');如何查看當(dāng)前數(shù)據(jù)庫的字符集: SQL>select userenv('language') from dual; SQL>select userenv('lang') from dual;怎么判斷當(dāng)前正在使用何種SQL優(yōu)化方式: 用explain plan產(chǎn)生EXPLAIN PLAN，檢查PLAN_T

9、ABLE中ID=0的POSITION列的值。 SQL>select decode(nvl(position,-1),-1,'RBO',1,'CBO') from plan_table where id=0;如何查看系統(tǒng)當(dāng)前最新的SCN號(hào)：SQL>select max(ktuxescnw * power(2,32) + ktuxescnb) from x$ktuxe;在ORACLE中查找TRACE文件的腳本:SQL>select u_dump.value | '/' | instance.value | '_ora_

10、9; | v$process.spid | nvl2(v$process.traceid, '_' | v$process.traceid, null ) | '.trc'"Trace File" from v$parameter u_dump cross join v$parameter instance cross join v$process join v$session on v$process.addr = v$session.paddr where u_ = 'user_dump_dest' an

11、d = 'instance_name' and v$session.audsid=sys_context('userenv','sessionid'); SQL>select d.value | '/ora_' | p.spid | '.trc' trace_file_namefrom (select p.spid from sys.v_$mystat m,sys.v_$session s,sys.v_$process p where m.statistic# = 1 a

12、nds.sid = m.sid and p.addr = s.paddr) p,(select value from sys.v_$parameter where name ='user_dump_dest') d; 如何查看客戶端登陸的IP地址:SQL>select sys_context('userenv','ip_address') from dual;如何在生產(chǎn)數(shù)據(jù)庫中創(chuàng)建一個(gè)追蹤客戶端IP地址的觸發(fā)器：SQL>create or replace trigger on_logon_triggerafter logon

13、 on databasebegin dbms_application_info.set_client_info(sys_context('userenv', 'ip_address');end;REM 記錄登陸信息的觸發(fā)器 CREATE OR REPLACE TRIGGER LOGON_HISTORY AFTER LOGON ON DATABASE -WHEN (USER='WACOS') -ONLY FOR USER 'WACOS' BEGIN insert into session_history select usernam

14、e,sid,serial#,AUDSID,OSUSER,ACTION,SYSDATE,null,SYS_CONTEXT('USERENV','IP_ADDRESS'),TERMINAL,machine,PROGRAM from v$session where audsid = userenv('sessionid'); END;查詢當(dāng)前日期: SQL> select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;查看所有表空間對(duì)應(yīng)的數(shù)據(jù)文件名：SQL>select

15、 distinct file_name,tablespace_name,AUTOEXTENSIBLE from dba_data_files;查看表空間的使用情況:SQL>select sum(bytes)/(1024*1024) as free_space,tablespace_name from dba_free_space group by tablespace_name;select a.tablespace_name,total,free,total-free used from (select tablespace_name,sum(bytes)/1024/1024 tota

16、l from dba_data_filesgroup by tablespace_name) a, (select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name;查看數(shù)據(jù)文件的hwm（可以resize的最小空間）和文件頭大小:SELECT v1.file_name,v1.file_id,num1 totle_space,num3 free_space,num1-num3 &quo

17、t;USED_SPACE(HWM)",nvl(num2,0) data_space,num1-num3-nvl(num2,0) file_headFROM (SELECT file_name,file_id,SUM(bytes) num1 FROM Dba_Data_Files GROUP BY file_name,file_id) v1,(SELECT file_id,SUM(bytes) num2 FROM dba_extents GROUP BY file_id) v2,(SELECT file_id,SUM(BYTES) num3 FROM DBA_FREE_SPACE GR

18、OUP BY file_id) v3WHERE v1.file_id=v2.file_id(+)AND v1.file_id=v3.file_id(+);數(shù)據(jù)文件大小及頭大小:SELECT v1.file_name,v1.file_id, num1 totle_space, num3 free_space, num1-num3 Used_space, nvl(num2,0) data_space, num1-num3-nvl(num2,0) file_head FROM (SELECT file_name,file_id,SUM(bytes) num1 FROM Dba_Data_Files

19、GROUP BY file_name,file_id) v1, (SELECT file_id,SUM(bytes) num2 FROM dba_extents GROUP BY file_id) v2, (SELECT file_id,SUM(BYTES) num3 FROM DBA_FREE_SPACE GROUP BY file_id) v3 WHERE v1.file_id=v2.file_id(+) AND v1.file_id=v3.file_id(+);(運(yùn)行以上查詢，我們可以如下信息： Totle_pace:該數(shù)據(jù)文件的總大小，字節(jié)為單位 Free_space:該數(shù)據(jù)文件的剩于

20、大小，字節(jié)為單位 Used_space:該數(shù)據(jù)文件的已用空間，字節(jié)為單位 Data_space:該數(shù)據(jù)文件中段數(shù)據(jù)占用空間，也就是數(shù)據(jù)空間，字節(jié)為單位 File_Head:該數(shù)據(jù)文件頭部占用空間，字節(jié)為單位)數(shù)據(jù)庫各個(gè)表空間增長情況的檢查：SQL>select A.tablespace_name,(1-(A.total)/B.total)*100 used_percentFrom (select tablespace_name,sum(bytes) total from dba_free_space group by tablespace_name) A,(select tablespa

21、ce_name,sum(bytes) total from dba_data_files group by tablespace_name) B where A.tablespace_name=B.tablespace_name; SQL>SELECT UPPER(F.TABLESPACE_NAME) "表空間名", D.TOT_GROOTTE_MB "表空間大小(M)",        D.TOT_GROOTTE_MB -

22、60;F.TOTAL_BYTES "已使用空間(M)", TO_CHAR(ROUND(D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') "使用比", F.TOTAL_BYTES "空閑空間(M)",        F.MAX_BYTES "最大塊

23、(M)" FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,    &#

24、160;   (SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD  GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME 數(shù)據(jù)庫對(duì)象下一擴(kuò)展與表空間

25、的free擴(kuò)展值的檢查：Disk Read最高的SQL語句的獲?。篠QL>select sql_text from (select * from v$sqlarea order by disk_reads)where rownum<=5; 查找前十條性能差的sqlSELECT * FROM (SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM  v$sqlarea ORDER BY disk_reads DESC)   WHERE

26、 ROWNUM<10 ; 等待時(shí)間最多的5個(gè)系統(tǒng)等待事件的獲?。篠QL>select * from (select * from v$system_event where event not like 'SQL%' order by total_waits desc) where rownum<=5; 查看當(dāng)前等待事件的會(huì)話:col username format a10set line 120col EVENT format a30select SE.Sid,s.Username,SE.Event,se.Total_Waits,SE.Ti

27、me_Waited,SE.Average_Waitfrom v$session S,v$session_event SE where S.Username is not null and SE.Sid=S.Sid and S.Status='ACTIVE' and SE.Event not like '%SQL*Net%' select sid, event, p1, p2, p3, wait_time, seconds_in_wait, state from v$session_wait where event not like '%mess

28、age%' and event not like 'SQL*Net%' and event not like '%timer%' and event != 'wakeup time manager'找到與所連接的會(huì)話有關(guān)的當(dāng)前等待事件：select SW.Sid,S.Username,SW.Event,SW.Wait_Time,SW.State,SW.Seconds_In_Wait SEC_IN_WAITfrom v$session S,v$session_wait SW where S.Username is not null and

29、SW.Sid=S.Sidand SW.event not like '%SQL*Net%' order by SW.Wait_Time Desc; Oracle所有回滾段狀態(tài)的檢查：SQL>select segment_name,owner,tablespace_name,initial_extent,next_extent,dba_rollback_segs.status from dba_rollback_segs,v$datafile where file_id=file#;Oracle回滾段擴(kuò)展信息的檢查:col name format a10set l

30、inesize 140select substr(name,1,40) name,extents,rssize,optsize,aveactive,extends,wraps,shrinks,hwmsizefrom v$rollname rn,v$rollstat rs where (rn.usn=rs.usn); extents:回滾段中的盤區(qū)數(shù)量。Rssize:以字節(jié)為單位的回滾段的尺寸。optsize：為optimal參數(shù)設(shè)定的值。Aveactive:從回滾段中刪除盤區(qū)時(shí)釋放的以字節(jié)為單位的平均空間的大小。Extends:系統(tǒng)為回滾段增加的盤區(qū)的次數(shù)。Shrinks:系統(tǒng)從回滾段中清除盤

31、區(qū)（即回滾段收縮）的次數(shù)。回滾段每次清除盤區(qū)時(shí)，系統(tǒng)可能會(huì)從這個(gè)回滾段中消除一個(gè)或多個(gè)盤區(qū)。Hwmsize:回滾段尺寸的上限，即回滾段曾經(jīng)達(dá)到的最大尺寸。(如果回滾段平均尺寸接近OPTIMAL的值，那么說明OPTIMAL的值設(shè)置正確，如果回滾段動(dòng)態(tài)增長次數(shù)或收縮次數(shù)很高，那么需要提高OPTIMAL的值) 查看回滾段的使用情況，哪個(gè)用戶正在使用回滾段的資源:select s.username, from v$transaction t,v$rollstat r,v$rollname u,v$session s where s.taddr=t.addr andt.xidus

32、n=r.usn and r.usn=u.usn order by s.username; 如何查看一下某個(gè)shared_server正在忙什么:SELECT a.username,a.machine,gram,a.sid,a.serial#,a.status,c.piece,c.sql_textFROM v$session a,v$process b,v$sqltext cWHERE b.spid=13161 AND b.addr=a.paddrAND a.sql_address=c.address(+) ORDER BY c.piece;   數(shù)據(jù)庫共

33、享池性能檢查:Select namespace,gets,gethitratio,pins,pinhitratio,reloads,檢查數(shù)據(jù)重載比率:select sum(reloads)/sum(pins)*100 "reload ratio" fromv$librarycache; 檢查數(shù)據(jù)字典的命中率:select 1-sum(getmisses)/sum(gets) "data dictionary hitratio" from v$rowcache;(對(duì)于library cache, gethitratio和pinhitratio應(yīng)該

34、大于90%,對(duì)于數(shù)據(jù)重載比率,reload ratio應(yīng)該小于1%,對(duì)于數(shù)據(jù)字典的命中率,data dictionary hit ratio應(yīng)該大于85%) 檢查共享內(nèi)存的剩余情況:select request_misses, request_failures from v$shared_pool_reserved; (對(duì)于共享內(nèi)存的剩余情況, request_misses 和request_failures應(yīng)該接近0) 數(shù)據(jù)高速緩沖區(qū)性能檢查:select 1-p.value/(b.value+c.value) "db buffer cache hitratio

35、" from v$sysstat p,v$sysstat b,v$sysstat c ='physical reads' and ='db block gets' ='consistent gets'檢查buffer pool HIT_RATIO執(zhí)行select name, (physical_reads/(db_block_gets+consistent_gets)"MISS_HIT_RATIO" FROM v$buffer_pool_statistics WHE

36、RE (db_block_gets+ consistent_gets)> 0;(正常時(shí)db buffer cache hit ratio 應(yīng)該大于90%,正常時(shí)buffer pool MISS_HIT_RATIO 應(yīng)該小于10%) 數(shù)據(jù)庫回滾段性能檢查:檢查Ratio執(zhí)行select sum(waits)* 100 /sum(gets) "Ratio", sum(waits) "Waits", sum(gets) "Gets" from v$rollstat;檢查count/value執(zhí)行:select class,c

37、ount from v$waitstat where class like '%undo%'select value from v$sysstat where name='consistent gets'(兩者的value值相除) 檢查average_wait執(zhí)行:select event,total_waits,time_waited,average_wait from v$system_event where event like '%undo%' 檢查RBS header get ratio執(zhí)行:select n.nam

38、e,s.usn,s.wraps, decode(s.waits,0,1,1- s.waits/s.gets)"RBS header get ratio" from v$rollstat s,v$rollname n where s.usn=n.usn;(正常時(shí)Ratio應(yīng)該小于1%, count/value應(yīng)該小于0.01%,average_wait最好為0，該值越小越好,RBS header get ratio應(yīng)該大于95%) 殺會(huì)話的腳本:select A.SID,B.SPID,A.SERIAL#,a.lockwait,A.USERNAME,A.OSUSER,

39、a.logon_time,a.last_call_et/3600 LAST_HOUR,A.STATUS, 'orakill '|sid|' '|spid HOST_COMMAND,'alter system kill session '''|A.sid|','|A.SERIAL#|'''' SQL_COMMANDfrom v$session A,V$PROCESS B where A.PADDR=B.ADDR AND SID>6; 查看排序段的性能:SQL>SE

40、LECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');  查看數(shù)據(jù)庫庫對(duì)象:select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;查看數(shù)據(jù)庫的版本:Select * from v$ver

41、sion;查看數(shù)據(jù)庫的創(chuàng)建日期和歸檔方式:Select Created, Log_Mode, Log_Mode From V$Database; 捕捉運(yùn)行很久的SQL:column username format a12 column opname format a16 column progress format a8 select username,sid,opname,round(sofar*100 / totalwork,0) | '%' as progress,time_remaining,sql_text from v$session_longops , v$sql

42、 where time_remaining <> 0 and sql_address=address and sql_hash_value = hash_value;查看數(shù)據(jù)表的參數(shù)信息:SELECT partition_name, high_value, high_value_length, tablespace_name,pct_free, pct_used, ini_trans, max_trans, initial_extent,next_extent, min_extent, max_extent, pct_increase, FREELISTS,freelist_gro

43、ups, LOGGING, BUFFER_POOL, num_rows, blocks,empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,last_analyzed FROM dba_tab_partitions WHERE table_name = :tname AND table_owner = :townerORDER BY partition_position; 查看還沒提交的事務(wù):select * from v$locked_object;select * from v$transaction;

44、0;查找object為哪些進(jìn)程所用:select p.spid,s.sid,s.serial# serial_num,s.username user_name,a.type object_type,s.osuser os_user_name,a.owner,a.object object_name,decode(sign(48 - command),1,to_char(command), 'Action Code #' | to_char(command) ) action,gram oracle_process,s.terminal terminal,gr

45、am program,s.status session_status from v$session s, v$access a, v$process p where s.paddr = p.addr and s.type = 'USER' and a.sid = s.sid and a.object='SUBSCRIBER_ATTR'order by s.username, s.osuser; 查看回滾段:SQL>col name format a10SQL>set linesize 100SQL>select rownum, sys

46、.dba_rollback_segs.segment_name Name, v$rollstat.extents Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs, v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes, sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs, v$rollname where v$rollname.n

47、ame(+) = sys.dba_rollback_segs.segment_name and v$rollstat.usn (+) = v$rollname.usn order by rownum; 耗資源的進(jìn)程(top session):select s.schemaname schema_name,decode(sign(48 - command), 1, to_char(command), 'Action Code #' | to_char(command) ) action,status session_status,s.osuser os_user_nam

48、e,s.sid,p.spid,s.serial# serial_num,nvl(s.username,'Oracle process') user_name,s.terminal terminal,gram program,st.value criteria_value from v$sesstat st,v$session s,v$process p where st.sid = s.sid and st.statistic# = to_number('38') and ('ALL'='ALL' or s.status

49、 ='ALL') and p.addr=s.paddr order by st.value desc,p.spid asc,s.username asc,s.osuser asc; 根據(jù)PID查找相應(yīng)的語句:SELECT a.username, a.machine,gram,a.sid,a.serial#,a.status,c.piece,c.sql_text根據(jù)SID找ORACLE的某個(gè)進(jìn)程:SQL> select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.p

50、addr=pro.addr; 監(jiān)控當(dāng)前數(shù)據(jù)庫誰在運(yùn)行什么SQL語句:SQL>SELECT osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece; 如何查看數(shù)據(jù)庫中某用戶，正在運(yùn)行什么SQL語句SQL>SELECT SQL_TEXT FROM V$SQLTEXT T, V$SESSION S WHERE T.ADDRESS=S.SQL_ADDRESS AND T.HASH_VALUE=S.SQL_

51、HASH_VALUE AND S.MACHINE='XXXXX' OR USERNAME='WACOS' 如何查出前臺(tái)正在發(fā)出的sql語句:SQL> select user_name,sql_text from v$open_cursor where sid in(select sid from (select sid,serial# from v$session where status='ACTIVE'); 查詢當(dāng)前所執(zhí)行的SQL語句： SQL> select program ,sql_address

52、 from v$session where paddr in (select addrfrom v$process where spid=3556); PROGRAM SQL_ADDRESS- -sqlplusctc20 (TNS V1-V3) 000000038FCB1A90 找出消耗CPU最高的進(jìn)程對(duì)應(yīng)的SQL語句：set line 240set verify offcolumn sid format 999column pid format 999 column S_# format 999column username format A9 heading "

53、;ORA User"column program format a29column SQL format a60COLUMN OSname format a9 Heading "OS User"SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,S.osuser osname,P.serial# S_#,P.terminal,P.program program,CPU用率最高的2條SQL語句的獲取執(zhí)行：top，通過top獲得CPU占用率最高的進(jìn)程的pid。SQL>select sql_text

54、,spid,v$gram,process from v$sqlarea,v$session,v$process where v$sqlarea.address=v$session.sql_address and v$sqlarea.hash_value=v$session.sql_hash_valueand v$session.paddr=v$process.addr and v$process.spid in (pid); col machine format a30 col program format a40 set line 200 SQL>sel

55、ect sid,serial# ,username,osuser,machine,program,process,to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') from v$session where paddr in(select addr from v$process where spid in($spid); select sql_text from v$sqltext_with_newlines where hash_value=(select SQL_HASH_VALUE from v$session wh

56、ere sid=&sid) order by piece; 查看鎖（lock）情況:SQL>select /*+ RULE */ ls.osuser os_user_name, ls.username user_name, decode(ls.type,'RW','Row wait enqueue lock','TM','DML enqueue lock','TX','Transaction enqueue lock','UL','User suppl

57、ied lock') lock_type,o.object_name object,decode(ls.lmode, 1, null, 2,'Row Share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive',null)lock_mode,o.owner,ls.sid,ls.serial# serial_num,ls.id1,ls.id2 from sys.dba_objects o,(select s.osuser,s.

58、username,l.type,l.lmode,s.sid,s.serial#,l.id1,l.id2 from v$session s,v$lock l where s.sid=l.sid)ls where o.object_id=ls.id1 and o.owner<>'SYS' order by o.owner, o.object_name; SQL>select  sys.v_$session.osuser,sys.v_$session.machine,v$lock.sid, sys.v_$session.serial#,

59、decode(v$lock.type,'MR','Media Recovery', 'RT','Redo Thread','UN','User Name','TX', 'Transaction','TM','DML', 'UL','PL/SQL User Lock','DX','Distributed Xacti

60、on','CF','Control File', 'IS','Instance State','FS','File Set','IR','Instance Recovery', 'ST','Disk Space Transaction','TS','Temp Segment','IV','Library&

61、#160;Cache Invalida-tion','LS','Log Start or Switch','RW','Row Wait','SQ','Sequence Number','TE','Extend Table','TT','Temp Table','Unknown') LockType, rtrim(object

62、_type) | ' ' | rtrim(owner) | '.' | object_name object_name,decode(lmode, 0, 'None',1, 'Null',2, 'Row-S',3, 'Row-X',4, 'Share', 5, 'S/Row-X',6, &#

63、39;Exclusive','Unknown') LockMode,decode(request, 0, 'None',1, 'Null',2, 'Row-S',3, 'Row-X', 4, 'Share',5, 'S/Row-X', 6, 'Exclusive', 'Unknown') RequestMode,ctime, 

64、block b from v$lock, all_objects, sys.v_$session where v$Lock.sid > 6 and sys.v_$session.sid = v$lock.sid and v$lock.id1 = all_objects.object_id; 以DBA角色, 查看當(dāng)前數(shù)據(jù)庫里鎖的情況可以用如下SQL語句： col owner for a12 col object_name for a16 select b.owner,b.object_name,l.session_id,l.locked_mode from v$locked_object l, dba_