Oracle数据库SQL语句操作大全汇总

数据库各个表空间增长情况的检查： SQL >SELECT A.TABLESPACE_NAME,( 1-(A.TOTAL)/B.TOTAL)*100 USED_PERCENTFROM (SELECT TABLESPACE_NAME,SUM (BYTES) TOTAL FROM DBA_FREE_SPACE GROUP BYTABLESPACE_NAME) A,(SELECT TABLESPACE_NAME,SUM (BYTES) TOTAL FROMDBA_DATA_FILES GROUP BY TABLESPACE_NAME) B WHEREA.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 (SELECTTABLESPACE_NAME, ROUND(SUM (BYTES) / (1024 * 1024 ), 2) TOTAL_BYTES, ROUND(MAX(BYTES) / (1024 * 1024 ), 2) MAX_BYTES FROM SYS .DBA_FREE_SPACE GROUP BYTABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME,ROUND(SUM (DD.BYTES) / ( 1024 *1024 ), 2 ) TOT_GROOTTE_MB FROM SYS .DBA_DATA_FILES DD GROUP BYDD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY4DESC ; 查看各个表空间占用磁盘情况: 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)* 100PERCENT FROM DBA_FREE_SPACE A,DBA_DATA_FILES BWHERE A.FILE_ID=B.FILE_ID GROUP BY B.TABLESPACE_NAME,B.FILE_ID,B.BYTES ORDER BYB.FILE_ID; 数据库对象下一扩展与表空间的 free扩展值的检查： SQL>SELECT A.TABLE_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME FROM ALL_TABLESA,(SELECT TABLESPACE_NAME, MAX (BYTES) AS BIG_CHUNK FROM DBA_FREE_SPACE GROUPBY TABLESPACE_NAME ) F WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME ANDA.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) ASBIG_CHUNK FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F WHEREF.TABLESPACE_NAME = A.TABLESPACE_NAME AND A.NEXT_EXTENT > F.BIG_CHUNK; DiskRead最高的SQL 语句的获取： SQL >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 ROWNUM <10 ;等待时间最多的 5 个系统等待事件的获取： SQL>SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE'SQL%'ORDERBY TOTAL_WAITS DESC ) WHERE ROWNUM <=5 ;查看当前等待事件的会话 :COL USERNAME FORMAT A10 SET LINE120 COL EVENT FORMAT A30 SELECT SE.SID,S.USERNAME,SE.EVENT,SE.TOTAL_WAITS,SE.TIME_WAITED,SE.AVERAGE_WAITFROM V$SESSION S,V$SESSION_EVENT SE WHERE S.USERNAME IS NOT NULL ANDSE.SID=S.SID AND S.STATUS= 'ACTIVE'AND SE.EVENT NOT LIKE '%SQL*NET%' ; SELECTSID, EVENT, P1, P2, P3, WAIT_TIME, SECONDS_IN_WAIT, STATE FROM V$SESSION_WAITWHERE EVENT NOT LIKE'%MESSAGE%'AND EVENT NOT LIKE 'SQL*NET%'AND EVENT NOT LIKE'%TIMER%' AND EVENT != 'WAKEUP TIME MANAGER'; 找到与所连接的会话有关的当前等待事件：

SELECTSW.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 ANDSW.SID=S.SID AND SW.EVENT NOT LIKE '%SQL*NET%' ORDER BY SW.WAIT_TIME DESC;Oracle 所有回滚段状态的检查：

SQL>SELECTSEGMENT_NAME,OWNER,TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,DBA_ROLLBACK_SEGS.STATUSFROM DBA_ROLLBACK_SEGS,V$DATAFILE WHERE FILE_ID=FILE #;Oracle 回滚段扩展信息的检查 :COL NAME FORMAT A10 SET LINESIZE140 SELECT SUBSTR(NAME , 1 , 40 ) NAME ,EXTENTS,RSSIZE,OPTSIZE,AVEACTIVE,EXTENDS,WRAPS,SHRINKS,HWMSIZE FROM V$ROLLNAMERN,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$ROLLNAMEU,V$SESSION S WHERE S.TADDR=T.ADDR AND T.XIDUSN=R.USN AND R.USN=U.USN ORDER BYS.USERNAME;如何查看一下某个 shared_server正在忙什么: SELECTA.USERNAME,A.MACHINE,A.PROGRAM,A.SID, A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXTFROM V$SESSION A,V$PROCESS B,V$SQLTEXT C WHERE B.SPID=13161 AND B.ADDR=A.PADDRAND A.SQL_ADDRESS=C.ADDRESS(+) ORDER BY C.PIECE;数据库共享池性能检查 :SELECT NAMESPACE,GETS,GETHITRATIO,PINS,PINHITRATIO,RELOADS,INVALIDATIONS FROMV$LIBRARYCACHE WHERE NAMESPACE IN ('SQLAREA', 'TABLE/PROCEDURE', 'BODY' ,'TRIGGER' ); 检查数据重载比率 : SELECT SUM (RELOADS)/SUM (PINS)*100"RELOADRATIO" FROM V$LIBRARYCACHE;检查数据字典的命中率 :SELECT1-SUM (GETMISSES)/SUM (GETS) "DATA DICTIONARY HIT RATIO" FROMV$ROWCACHE;( 对于 library cache, gethitratio 和pinhitratio 应该大于 90%, 对于数据重载比率,reload ratio应该小于 1%,对于数据字典的命中率,datadictionary hit ratio应该大于 85%)

检查共享内存的剩余情况 :SELECT REQUEST_MISSES, REQUEST_FAILURES FROM V$SHARED_POOL_RESERVED;( 对于共享内存的剩余情况,request_misses和 request_failures 应该接近 0)

数据高速缓冲区性能检查 :SELECT1-P.VALUE /(B.VALUE +C.VALUE ) "DB BUFFER CACHE HIT RATIO" FROMV$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 poolHIT_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; ( 正常时dbbuffer cache hit ratio 应该大于90%, 正常时buffer poolMISS_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 FROMV$SYSSTAT WHERE NAME ='CONSISTENT GETS' ; ( 两者的 value 值相除 )检查average_wait 执行 : SELECT EVENT,TOTAL_WAITS,TIME_WAITED,AVERAGE_WAIT FROMV$SYSTEM_EVENT WHERE EVENT LIKE'%UNDO%'; 检查 RBS header getratio执行 : 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$ROLLNAMEN WHERE S.USN=N.USN;( 正常时 Ratio 应该小于 1%,count/value 应该小于 0.01%,average_wait 最好为0，该值越小越好 ,RBSheader get ratio 应该大于95%)

杀会话的脚本 :SELECTA.SID,B.SPID,A.SERIAL#,A.LOCKWAIT,A.USERNAME,A.OSUSER,A.LOGON_TIME,A.LAST_CALL_ET/3600LAST_HOUR,A.STATUS,'ORAKILL ' ||SID|| ' ' ||SPID HOST_COMMAND,'ALTER SYSTEM KILL SESSION'''||A.SID|| ','||A.SERIAL#|| ''''SQL_COMMAND FROM V$SESSION A,V$PROCESS BWHERE 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 、查看数据库的创建日期和归档方式:SELECTCREATED, LOG_MODE, LOG_MODE FROM V$DATABASE;

10 、捕捉运行很久的SQL:COLUMN USERNAME FORMAT A12 COLUMN OPNAME FORMAT A16 COLUMN PROGRESS FORMATA8 SELECT USERNAME,SID,OPNAME,ROUND(SOFAR*100/ TOTALWORK, 0 ) || '%' ASPROGRESS,TIME_REMAINING,SQL_TEXT FROM V$SESSION_LONGOPS , V$SQL WHERETIME_REMAINING <>0AND 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 FROMDBA_TAB_PARTITIONS--WHERE TABLE_NAME = :TNAME AND TABLE_OWNER = :TOWNERORDERBY 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.TYPEOBJECT_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.STATUSSESSION_STATUS FROM V$SESSION S, V$ACCESS A, V$PROCESS P WHERE S.PADDR = P.ADDRAND S.TYPE ='USER' AND A.SID = S.SID AND A.OBJECT ='SUBSCRIBER_ATTR'ORDER BYS.USERNAME, S.OSUSER; 14、查看回滚段 : SQL >COL NAME FORMAT A10SQL >SET LINESIZE100 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.STATUSSTATUS FROM V$ROLLSTAT, SYS .DBA_ROLLBACK_SEGS, V$ROLLNAME WHEREV$ROLLNAME.NAME (+) = SYS .DBA_ROLLBACK_SEGS.SEGMENT_NAME AND V$ROLLSTAT.USN(+) = V$ROLLNAME.USN ORDER BY ROWNUM ; 15 、耗资源的进程(topsession): SELECT S.SCHEMANAME SCHEMA_NAME,DECODE(SIGN(48 - COMMAND), 1, TO_CHAR(COMMAND),'ACTIONCODE #' || TO_CHAR(COMMAND) ) ACTION,STATUS SESSION_STATUS,S.OSUSEROS_USER_NAME,S.SID,P.SPID,S.SERIAL# SERIAL_NUM,NVL(S.USERNAME,'[ORACLEPROCESS]') USER_NAME,S.TERMINAL TERMINAL,S.PROGRAM PROGRAM,ST.VALUECRITERIA_VALUE FROM V$SESSTAT ST,V$SESSION S,V$PROCESS P WHERE ST.SID = S.SIDAND ST.STATISTIC# = TO_NUMBER('38') AND ( 'ALL' = 'ALL'OR S.STATUS ='ALL' ) ANDP.ADDR=S.PADDR ORDER BY ST.VALUE DESC ,P.SPID ASC ,S.USERNAME ASC ,S.OSUSER ASC;根据 PID 查找相应的语句 :

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

SQL语句大全—查看数据（三）

2007年08月16日 星期四 上午02:17

 

根据  SID 找 ORACLE 的某个进程 :  SQL > SELECT PRO.SPID FROM V$SESSION SES,V$PROCESS PRO WHERE SES.SID=21AND  SES.PADDR=PRO.ADDR; 监控当前数据库谁在运行什么SQL 语句 :  SQL >SELECT OSUSER, USERNAME, SQL_TEXT FROM V$SESSION A, V$SQLTEXT B WHERE  A.SQL_ADDRESS =B.ADDRESS ORDER BY ADDRESS, PIECE;如何查看数据库中某用户，正在运行什么  SQL 语句 SQL >SELECT SQL_TEXT FROM V$SQLTEXT T, V$SESSION S  WHERE T.ADDRESS=S.SQL_ADDRESS AND T.HASH_VALUE=S.SQL_HASH_VALUE AND  S.MACHINE='XXXXX'OR USERNAME='WACOS' ;如何查出前台正在发出的  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')); 查询当前所执行的  SQL 语句： SQL > SELECT PROGRAM ,SQL_ADDRESS FROM V$SESSION  WHERE PADDR IN (SELECT ADDR FROM V$PROCESS WHERE SPID=3556); PROGRAM  SQL_ADDRESS ------------------------------------------------  ----------------SQLPLUS@CTC20 (TNS V1-V3) 000000038 FCB1A90 SQL >  SELECT SQL_TEXT FROM V$SQLAREA WHERE ADDRESS='000000038FCB1A90'; 找出消耗  CPU 最高的进程对应的 SQL 语句：SET LINE  240SET VERIFY OFF COLUMN SID FORMAT 999COLUMN PID FORMAT 999 COLUMN S_#  FORMAT999 COLUMN USERNAME FORMAT A9 HEADING "ORA USER" COLUMN  PROGRAM FORMAT A29 COLUMN SQL FORMAT A60 COLUMN 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,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT,1,  80)) SQLFROM V$PROCESS P, V$SESSION S,V$SQLAREA A WHERE P.ADDR = S.PADDR AND  S.SQL_ADDRESS = A.ADDRESS (+) AND P.SPID LIKE'%&1%'; ENTER VALUE FOR 1:  PID¡ （这里输入占用 CPU 最高的进程对应的 PID ）  SET TERMOUT OFF SPOOL MAXCPU.TXT SELECT'++' ||S.USERNAME  USERNAME,RTRIM(REPLACE (A.SQL_TEXT,CHR(10), '' ))|| ';'FROMV$PROCESS P,  V$SESSION S,V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS  (+) AND P.SPID LIKE'%&&1%'; Enter value for 1: PID（这里输入占用  CPU 最高的进程对应的 PID ） spool off( 这句放在最后执行) CPU  用率最高的 2条SQL 语句的获取 执行：top  ，通过top 获得 CPU占用率最高的进程的  pid。 SQL>SELECT SQL_TEXT,SPID,V$SESSION.PROGRAM,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_VALUE AND  V$SESSION.PADDR=V$PROCESS.ADDR AND V$PROCESS.SPID IN (PID); COL MACHINE  FORMAT A30 COL PROGRAM FORMAT A40 SET LINE200 SQL >SELECT 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 WHERE SID=&SID) ORDER BY  PIECE; 16 、查看锁 （ 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 SUPPLIED 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.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#, DECODE(V$LOCK.TYPE , 'MR','MEDIA RECOVERY' , 'RT' ,  'REDO THREAD', 'UN' , 'USER NAME', 'TX' , 'TRANSACTION', 'TM' , 'DML', 'UL' ,  'PL/SQL USER LOCK', 'DX' , 'DISTRIBUTED XACTION', 'CF' , 'CONTROL FILE', 'IS'  , 'INSTANCE STATE', 'FS' , 'FILE SET', 'IR' , 'INSTANCE RECOVERY', 'ST' ,  'DISK SPACE TRANSACTION', 'TS' , 'TEMP SEGMENT', 'IV' , 'LIBRARY CACHE  INVALIDA-TION', 'LS' , 'LOG START OR SWITCH', 'RW' , 'ROW WAIT', 'SQ' ,  'SEQUENCE NUMBER', 'TE' , 'EXTEND TABLE', 'TT' , 'TEMP TABLE', 'UNKNOWN' )  LOCKTYPE, RTRIM(OBJECT_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 , '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, 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  角色 , 查看当前数据库里锁的情况可以用如下 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_OBJECTS B WHERE B.OBJECT_ID=L.OBJECT_ID; SQL >SELECT  T2.USERNAME,T2.SID,T2.SERIAL#,T2.LOGON_TIME FROM V$LOCKED_OBJECT T1,V$SESSION  T2 WHERE T1.SESSION_ID=T2.SID ORDER BY T2.LOGON_TIME; SQL  >SELECT SQL_ADDRESS FROM V$SESSION WHERE SID=; SQL  >SELECT * FROM V$SQLTEXT WHERE ADDRESS=; SQL  >SELECT COMMAND_TYPE,PIECE,SQL_TEXT FROM V$SQLTEXT WHERE ADDRESS=(SELECT  SQL_ADDRESS FROM V$SESSION A WHERE SID=18); SQL  >SELECT OBJECT_ID FROM V$LOCKED_OBJECT; SQL  >SELECT OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_ID=''; 如果有长期出现的一列，可能是没有释放的锁。我们可以用下面SQL  语句杀掉长期没有释放非正常的锁：SQL >ALTER SYSTEM KILL  SESSION'SID,SERIAL#'; 17、查看等待（ wait ）情况 :  SQL >SELECT V$WAITSTAT.CLASS ,V$WAITSTAT.COUNT COUNT , SUM  (V$SYSSTAT.VALUE ) SUM_VALUE FROM V$WAITSTAT,V$SYSSTAT WHERE V$SYSSTAT.NAME  IN ('DB BLOCK GETS' , 'CONSISTENT GETS' ) GROUP BY V$WAITSTAT.CLASS  ,V$WAITSTAT.COUNT ; 18、查看 sga 情况 : SQL  >SELECT NAME , BYTES FROM SYS .V_$SGASTAT ORDER BY NAME ASC ;19 、查看  catched object: SQL >SELECT OWNER,NAME ,DB_LINK,NAMESPACE,TYPE  ,SHARABLE_MEM,LOADS, EXECUTIONS,LOCKS,PINS,KEPT FROM V$DB_OBJECT_CACHE;20 、查看  V$SQLAREA: SQL >SELECT  SQL_TEXT,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM,SORTS, VERSION_COUNT,LOADED_VERSIONS,OPEN_VERSIONS,USERS_OPENING,EXECUTIONS,  USERS_EXECUTING,LOADS,FIRST_LOAD_TIME,INVALIDATIONS,PARSE_CALLS,  DISK_READS,BUFFER_GETS,ROWS_PROCESSED FROM V$SQLAREA; 21、查看  object 分类数量 : SELECT DECODE(O.TYPE #,1, 'INDEX', 2 , 'TABLE' , 3 ,  'CLUSTER', 4 , 'VIEW' , 5 , 'SYNONYM', 6 , 'SEQUENCE' ,'OTHER' ) OBJECT_TYPE  , COUNT (*) QUANTITY FROM SYS .OBJ$ O WHERE O.TYPE # >1GROUP BY  DECODE(O.TYPE #, 1 , 'INDEX', 2 , 'TABLE' , 3 , 'CLUSTER', 4 , 'VIEW' , 5 ,  'SYNONYM', 6 , 'SEQUENCE' ,'OTHER' ) UNION SELECT'COLUMN' , COUNT (*) FROM  SYS .COL$ UNION SELECT'DB LINK' , COUNT (*) FROM ALL_OBJECTS; 22、有关  connection 的相关信息 :1）查看有哪些用户连接 SELECT  S.OSUSER OS_USER_NAME,DECODE(SIGN(48 - COMMAND), 1,TO_CHAR(COMMAND), 'ACTION  CODE #'|| TO_CHAR(COMMAND))ACTION,P.PROGRAM ORACLE_PROCESS, STATUS  SESSION_STATUS,S.TERMINAL TERMINAL,S.PROGRAM PROGRAM, S.USERNAME  USER_NAME,S.FIXED_TABLE_SEQUENCE ACTIVITY_METER,''QUERY , 0  MEMORY,0MAX_MEMORY, 0 CPU_USAGE,S.SID,S.SERIAL# SERIAL_NUM FROM V$SESSION  S,V$PROCESS P WHERE S.PADDR=P.ADDR AND S.TYPE ='USER'ORDER BY S.USERNAME,  S.OSUSER; 2）根据 v.sid 查看对应连接的资源占用等情况 SELECT N.NAME  ,V.VALUE ,N.CLASS ,N.STATISTIC# FROM V$STATNAME N,V$SESSTAT V WHERE  V.SID=18AND V.STATISTIC# = N.STATISTIC# ORDER BY N.CLASS , N.STATISTIC#;3 ）根据  sid 查看对应连接正在运行的 sql SELECT /*+ PUSH_SUBQ */COMMAND_TYPE,SQL_TEXT,SHARABLE_MEM,  PERSISTENT_MEM,RUNTIME_MEM,SORTS,VERSION_COUNT,LOADED_VERSIONS,OPEN_VERSIONS,USERS_OPENING,EXECUTIONS,  USERS_EXECUTING,LOADS,FIRST_LOAD_TIME,INVALIDATIONS,PARSE_CALLS,DISK_READS,BUFFER_GETS,ROWS_PROCESSED,SYSDATE  START_TIME,SYSDATE FINISH_TIME, '>' || ADDRESS SQL_ADDRESS,'N'STATUS FROM  V$SQLAREA WHERE ADDRESS = (SELECT SQL_ADDRESS FROM V$SESSION WHERE SID=8); 根据  pid查看 sql 语句: SELECT  SQL_TEXT FROM V$SQL WHERE ADDRESS IN (SELECT SQL_ADDRESS FROM V$SESSION WHERE  SID IN (SELECT SID FROM V$SESSION WHERE PADDR IN (SELECT ADDR FROM V$PROCESS  WHERE SPID=&PID)));

23、查询表空间使用情况: SELECTA.TABLESPACE_NAME "空间名称", 100-ROUND((NVL(B.BYTES_FREE,0)/A.BYTES_ALLOC)*100 , 2 ) " 占用率 (%) ",ROUND(A.BYTES_ALLOC/1024 / 1024 , 2 ) " 容量 (M) ",ROUND(NVL(B.BYTES_FREE,0 )/ 1024 / 1024, 2 ) 空闲(M) ",ROUND((A.BYTES_ALLOC-NVL(B.BYTES_FREE,0 ))/ 1024 / 1024, 2 ) " 使用 (M)", LARGEST " 最大扩展段 (M)",TO_CHAR(SYSDATE ,'YYYY-MM-DDHH24:MI:SS' ) "采样时间 " FROM (SELECT F.TABLESPACE_NAME,SUM(F.BYTES) BYTES_ALLOC,SUM (DECODE(F.AUTOEXTENSIBLE,'YES',F.MAXBYTES, 'NO',F.BYTES)) MAXBYTES FROM DBA_DATA_FILES F GROUP BY TABLESPACE_NAME) A,(SELECTF.TABLESPACE_NAME,SUM (F.BYTES) BYTES_FREE FROM DBA_FREE_SPACE F GROUP BYTABLESPACE_NAME) B,(SELECT ROUND(MAX (FF.LENGTH)*16 / 1024 ,2 ) LARGEST,TS.NAMETABLESPACE_NAME FROM SYS .FET$ FF, SYS .FILE $ TF,SYS .TS$ TS WHERETS.TS#=FF.TS# AND FF.FILE #=TF.RELFILE# AND TS.TS#=TF.TS# GROUP BY TS.NAME ,TF.BLOCKS) C WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND A.TABLESPACE_NAME= C.TABLESPACE_NAME; 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, (SELECTDD.TABLESPACE_NAME, ROUND(SUM (DD.BYTES) / ( 1024* 1024), 2 ) TOT_GROOTTE_MBFROM SYS .DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERED.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY4 DESC ; 24 、查询表空间的碎片程度 :SQL >SELECT TABLESPACE_NAME,COUNT (TABLESPACE_NAME) FROM DBA_FREE_SPACEGROUP BY

原文地址：http://blog.csdn.net/sutku/article/details/7900876

如需转载请注明出处，尊重他人劳动成果