常用sql語句

轉自：[url]http://www.cnblogs.com/jimeper/archive/2007/05/13/744813.html[/url]

檢視表空間的名稱及大小:

SQL> SELECT T.TABLESPACE_NAME, ROUND(SUM(BYTES/(1024 * 1024)), 0) TS_SIZE

 FROM 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> SELECT 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 V

 WHERE R.SEGMENT_ID = V.USN(+)

 ORDER BY SEGMENT_NAME;

如何檢視某個復原段裡面，跑的什麼事物或者正在執行什麼sql語句:

SQL> SELECT D.SQL_TEXT, A.NAME

 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;

(備注：你要看哪個，就把usn=?寫成幾就行了)

檢視控制檔案:

SQL> SELECT * FROM V$CONTROLFILE;

檢視日志檔案:

SQL> COL MEMBER FORMAT A50

SQL>SELECT * FROM V$LOGFILE;

如何檢視目前SQL*PLUS使用者的sid和serial#:

SQL>SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE AUDSID=USERENV('SESSIONID');

如何檢視目前資料庫的字元集: 

SQL>SELECT USERENV('LANGUAGE') FROM DUAL;

SQL>SELECT USERENV('LANG') FROM DUAL;

怎麼判斷目前正在使用何種SQL優化方式:

用EXPLAIN PLAN產生EXPLAIN PLAN¡檢查PLAN_TABLE中ID=0的POSITION列的值

SQL>SELECT DECODE(NVL(POSITION,-1),-1,'RBO',1,'CBO') FROM PLAN_TABLE WHERE ID=0;

如何檢視系統目前最新的SCN号：

SQL>SELECT MAX(KTUXESCNW * POWER(2,32) + KTUXESCNB) FROM X$KTUXE;

在ORACLE中查找TRACE檔案的腳本:

SQL>SELECT U_DUMP.VALUE || '/' || INSTANCE.VALUE || '_ORA_' ||

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_DUMP.NAME = 'USER_DUMP_DEST' AND

INSTANCE.NAME = 'INSTANCE_NAME' AND V$SESSION.AUDSID=SYS_CONTEXT('USERENV','SESSIONID');

SQL>SELECT D.VALUE || '/ORA_' || P.SPID || '.TRC' TRACE_FILE_NAME

FROM (SELECT P.SPID FROM SYS.V_$MYSTAT M,SYS.V_$SESSION S,

SYS.V_$PROCESS P WHERE M.STATISTIC# = 1 AND

S.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;

如何在生産資料庫中建立一個追蹤用戶端IP位址的觸發器：

SQL>CREATE OR REPLACE TRIGGER ON_LOGON_TRIGGER AFTER LOGON ON DATABASE

BEGIN

 DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('USERENV', 'IP_ADDRESS'));

END;

REM 記錄登陸資訊的觸發器

CREATE OR REPLACE TRIGGER LOGON_HISTORY

AFTER LOGON ON DATABASE --WHEN (USER='WACOS') --ONLY FOR USER 'WACOS'

INSERT INTO SESSION_HISTORY SELECT USERNAME,SID,SERIAL#,AUDSID,OSUSER,ACTION,SYSDATE,NULL,SYS_CONTEXT('USERENV','IP_ADDRESS'),TERMINAL,MACHINE,PROGRAM FROM V$SESSION WHERE AUDSID = USERENV('SESSIONID');

查詢目前日期:

SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD,HH24:MI:SS') FROM DUAL;

檢視所有表空間對應的資料檔案名：

SQL>SELECT 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;

SQL>SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,

(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"

FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

COLUMN TABLESPACE_NAME FORMAT A18;

COLUMN SUM_M FORMAT A12;

COLUMN USED_M FORMAT A12;

COLUMN FREE_M FORMAT A12;

COLUMN PTO_M FORMAT 9.99;

SELECT S.TABLESPACE_NAME,CEIL(SUM(S.BYTES/1024/1024))||'M' SUM_M,CEIL(SUM(S.USEDSPACE/1024/1024))||'M' USED_M,CEIL(SUM(S.FREESPACE/1024/1024))||'M' FREE_M, SUM(S.USEDSPACE)/SUM(S.BYTES) PTUSED FROM (SELECT B.FILE_ID,B.TABLESPACE_NAME,B.BYTES, (B.BYTES-SUM(NVL(A.BYTES,0))) USEDSPACE, SUM(NVL(A.BYTES,0)) FREESPACE,(SUM(NVL(A.BYTES,0))/(B.BYTES)) * 100 FREEPERCENTRATIO FROM SYS.DBA_FREE_SPACE A,SYS.DBA_DATA_FILES B WHERE A.FILE_ID(+)=B.FILE_ID GROUP BY B.FILE_ID,B.TABLESPACE_NAME,B.BYTES ORDER BY B.TABLESPACE_NAME) S GROUP BY S.TABLESPACE_NAME ORDER BY SUM(S.FREESPACE)/SUM(S.BYTES) DESC;

檢視資料檔案的hwm（可以resize的最小空間）和檔案頭大小:

SELECT V1.FILE_NAME,V1.FILE_ID,NUM1 TOTLE_SPACE,NUM3 FREE_SPACE,

NUM1-NUM3 "USED_SPACE(HWM)",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 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(+);

資料檔案大小及頭大小:

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

WHERE V1.FILE_ID=V2.FILE_ID(+)

AND V1.FILE_ID=V3.FILE_ID(+);

(運作以上查詢，我們可以如下資訊：

Totle_pace:該資料檔案的總大小，位元組為機關

Free_space:該資料檔案的剩于大小，位元組為機關

Used_space:該資料檔案的已用空間，位元組為機關

Data_space:該資料檔案中段資料占用空間，也就是資料空間，位元組為機關

File_Head:該資料檔案頭部占用空間，位元組為機關)

資料庫各個表空間增長情況的檢查：

SQL>SELECT A.TABLESPACE_NAME,(1-(A.TOTAL)/B.TOTAL)*100 USED_PERCENT

FROM (SELECT TABLESPACE_NAME,SUM(BYTES) TOTAL FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) A,(SELECT TABLESPACE_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 - 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 "最大塊(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,

       (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

ORDER BY 4 DESC;

檢視各個表空間占用磁盤情況:  

SQL>COL TABLESPACE_NAME FORMAT A20;

SQL>SELECT B.FILE_ID FILE_ID,

B.TABLESPACE_NAME TABLESPACE_NAME,

B.BYTES BYTES,

(B.BYTES-SUM(NVL(A.BYTES,0))) USED,

 SUM(NVL(A.BYTES,0)) FREE,

 SUM(NVL(A.BYTES,0))/(B.BYTES)*100 PERCENT

     FROM DBA_FREE_SPACE A,DBA_DATA_FILES B 

     WHERE A.FILE_ID=B.FILE_ID 

     GROUP BY B.TABLESPACE_NAME,B.FILE_ID,B.BYTES 

     ORDER BY B.FILE_ID;

資料庫對象下一擴充與表空間的free擴充值的檢查：

SQL>SELECT A.TABLE_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME

FROM ALL_TABLES A,(SELECT TABLESPACE_NAME, MAX(BYTES) AS BIG_CHUNK

FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME AND A.NEXT_EXTENT > F.BIG_CHUNK

UNION SELECT A.INDEX_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME

FROM ALL_INDEXES A,(SELECT TABLESPACE_NAME, MAX(BYTES) AS BIG_CHUNK

FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME AND A.NEXT_EXTENT > F.BIG_CHUNK;

Disk Read最高的SQL語句的擷取：

SQL>SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)

WHERE ROWNUM<=5;

查找前十條性能差的sql

SELECT * FROM (SELECT PARSING_USER_ID

 EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,

SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC) 

 WHERE ROWNUM<10 ;

等待時間最多的5個系統等待事件的擷取：

SQL>SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;

檢視目前等待事件的會話:

COL USERNAME FORMAT A10

SET LINE 120

COL EVENT FORMAT A30

SELECT SE.SID,S.USERNAME,SE.EVENT,SE.TOTAL_WAITS,SE.TIME_WAITED,SE.AVERAGE_WAIT

FROM 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 '%MESSAGE%' AND EVENT NOT LIKE 'SQL*NET%' AND EVENT NOT LIKE '%TIMER%' AND EVENT != 'WAKEUP TIME MANAGER';

找到與所連接配接的會話有關的目前等待事件：

SELECT SW.SID,S.USERNAME,SW.EVENT,SW.WAIT_TIME,SW.STATE,SW.SECONDS_IN_WAIT SEC_IN_WAIT

FROM V$SESSION S,V$SESSION_WAIT SW WHERE S.USERNAME IS NOT NULL AND SW.SID=S.SID

AND SW.EVENT NOT LIKE '%SQL*NET%' ORDER BY SW.WAIT_TIME DESC;

Oracle所有復原段狀态的檢查：

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復原段擴充資訊的檢查:

COL NAME FORMAT A10

SET LINESIZE 140        

SELECT SUBSTR(NAME,1,40) NAME,EXTENTS,RSSIZE,OPTSIZE,AVEACTIVE,EXTENDS,WRAPS,SHRINKS,HWMSIZE

FROM V$ROLLNAME RN,V$ROLLSTAT RS WHERE (RN.USN=RS.USN);

EXTENTS:復原段中的盤區數量。

Rssize:以位元組為機關的復原段的尺寸。

optsize：為optimal參數設定的值。

Aveactive:從復原段中删除盤區時釋放的以位元組為機關的平均空間的大小。

Extends:系統為復原段增加的盤區的次數。

Shrinks:系統從復原段中清除盤區（即復原段收縮）的次數。復原段每次清除盤區時，系統可能會從這個復原段中消除一個或多個盤區。

Hwmsize:復原段尺寸的上限，即復原段曾經達到的最大尺寸。

(如果復原段平均尺寸接近OPTIMAL的值，那麼說明OPTIMAL的值設定正确，如果復原段動态增長次數或收縮次數很高，那麼需要提高OPTIMAL的值)

檢視復原段的使用情況，哪個使用者正在使用復原段的資源:

SELECT S.USERNAME, U.NAME FROM V$TRANSACTION T,V$ROLLSTAT R,

V$ROLLNAME U,V$SESSION S WHERE S.TADDR=T.ADDR AND

T.XIDUSN=R.USN AND R.USN=U.USN ORDER BY S.USERNAME;

如何檢視一下某個shared_server正在忙什麼:

SELECT A.USERNAME,A.MACHINE,A.PROGRAM,A.SID,

A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXT

FROM V$SESSION A,V$PROCESS B,V$SQLTEXT C

WHERE B.SPID=13161 AND B.ADDR=A.PADDR

AND A.SQL_ADDRESS=C.ADDRESS(+) ORDER BY C.PIECE; 

資料庫共享池性能檢查:

SELECT NAMESPACE,GETS,GETHITRATIO,PINS,PINHITRATIO,RELOADS,INVALIDATIONS FROM V$LIBRARYCACHE WHERE NAMESPACE IN('SQLAREA','TABLE/PROCEDURE','BODY','TRIGGER');

檢查資料重載比率:

SELECT SUM(RELOADS)/SUM(PINS)*100 "RELOAD RATIO" FROM

V$LIBRARYCACHE;

檢查資料字典的命中率:

SELECT 1-SUM(GETMISSES)/SUM(GETS) "DATA DICTIONARY HIT

RATIO" FROM V$ROWCACHE;

(對于library cache, gethitratio和pinhitratio應該大于90%,對于資料重載比率,reload ratio應該小于1%,對于資料字典的命中率,data dictionary hit ratio應該大于85%)

檢查共享記憶體的剩餘情況:

SELECT REQUEST_MISSES, REQUEST_FAILURES FROM V$SHARED_POOL_RESERVED; 

(對于共享記憶體的剩餘情況, request_misses 和request_failures應該接近0)

資料高速緩沖區性能檢查:

SELECT 1-P.VALUE/(B.VALUE+C.VALUE) "DB BUFFER CACHE HIT RATIO" FROM V$SYSSTAT P,V$SYSSTAT B,V$SYSSTAT C WHERE P.NAME='PHYSICAL READS' AND B.NAME='DB BLOCK GETS' AND C.NAME='CONSISTENT GETS';

檢查buffer pool HIT_RATIO執行

SELECT NAME, (PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS)) "MISS_HIT_RATIO" FROM V$BUFFER_POOL_STATISTICS WHERE (DB_BLOCK_GETS+ CONSISTENT_GETS)> 0;

(正常時db buffer cache hit ratio 應該大于90%,正常時buffer pool MISS_HIT_RATIO 應該小于10%)

資料庫復原段性能檢查:

檢查Ratio執行

SELECT SUM(WAITS)* 100 /SUM(GETS) "RATIO", SUM(WAITS) "WAITS", SUM(GETS) "GETS" FROM V$ROLLSTAT;

檢查count/value執行:

SELECT CLASS,COUNT FROM V$WAITSTAT WHERE CLASS LIKE '%UNDO%';

SELECT VALUE FROM V$SYSSTAT WHERE NAME='CONSISTENT GETS';

(兩者的value值相除)

檢查average_wait執行:

SELECT EVENT,TOTAL_WAITS,TIME_WAITED,AVERAGE_WAIT FROM V$SYSTEM_EVENT WHERE EVENT LIKE '%UNDO%';

檢查RBS header get ratio執行:

SELECT N.NAME,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;

(正常時Ratio應該小于1%, count/value應該小于0.01%,average_wait最好為0，該值越小越好,RBS header get ratio應該大于95%)

殺會話的腳本:

SELECT A.SID,B.SPID,A.SERIAL#,A.LOCKWAIT,A.USERNAME,A.OSUSER,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_COMMAND FROM V$SESSION A,V$PROCESS B WHERE A.PADDR=B.ADDR AND SID>6;

檢視排序段的性能:

SQL>SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN ('SORTS (MEMORY)', 'SORTS (DISK)'); 

7、檢視資料庫庫對象:

SELECT OWNER, OBJECT_TYPE, STATUS, COUNT(*) COUNT# FROM ALL_OBJECTS GROUP BY OWNER, OBJECT_TYPE, STATUS;

8、檢視資料庫的版本:　

SELECT * FROM V$VERSION;

9、檢視資料庫的建立日期和歸檔方式:

SELECT CREATED, LOG_MODE, LOG_MODE FROM V$DATABASE;

10、捕捉運作很久的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 WHERE TIME_REMAINING <> 0 AND SQL_ADDRESS=ADDRESS AND SQL_HASH_VALUE = HASH_VALUE;

11、檢視資料表的參數資訊:

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_GROUPS, 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 = :TOWNER

ORDER BY PARTITION_POSITION;

12、檢視還沒送出的事務:

SELECT * FROM V$LOCKED_OBJECT;

SELECT * FROM V$TRANSACTION;

13、查找object為哪些程序所用:

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,

P.PROGRAM ORACLE_PROCESS,S.TERMINAL TERMINAL,S.PROGRAM 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;

14、檢視復原段:

SQL>COL NAME FORMAT A10

SQL>SET LINESIZE 100

SQL>SELECT ROWNUM, SYS.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.NAME(+) = SYS.DBA_ROLLBACK_SEGS.SEGMENT_NAME AND V$ROLLSTAT.USN (+) = V$ROLLNAME.USN ORDER BY ROWNUM;

15、耗資源的程序(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_NAME,S.SID,P.SPID,S.SERIAL# SERIAL_NUM,NVL(S.USERNAME,'[ORACLE PROCESS]') USER_NAME,S.TERMINAL TERMINAL,S.PROGRAM 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 ='ALL') AND P.ADDR=S.PADDR ORDER BY ST.VALUE DESC,P.SPID ASC,S.USERNAME ASC,S.OSUSER ASC;

根據PID查找相應的語句:

SELECT A.USERNAME, A.MACHINE,A.PROGRAM,A.SID,A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXT FROM V$SESSION A,V$PROCESS B,V$SQLTEXT C WHERE B.SPID=SPID AND B.ADDR=A.PADDR AND A.SQL_ADDRESS=C.ADDRESS(+) ORDER BY C.PIECE;