查看表空间的名称及大小:
 
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'
BEGIN
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' );
END;

查询当前日期 :
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
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(+);

( 运行以上查询，我们可以如下信息：
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;