ORACLE数据库日常维护手册(最全+最实用)

1、精品好资料学习推荐ORACLE日常维护手册査看数据库版本SELECT*FROM V$VERSION;査看数据库语言环境SELECTUSERENVC LANGUAGE)FROM DUAL:査看ORACLE实例状态SELECT INSTANCEJAME, HOST_AME, STARTUPIME, STATUS, DATABASE_STATUSFROM V$INSTANCE;査看ORACLE监听状态Isnrctl status査看数据库归档模式SELECTNAME, LOG.MODE, OPEN J10DE FROM V$DATABASE;査看回收站中对象SELECT OBJECT_AME, OR

2、IGINAL.XAME, TYPEFROMRECYCLEBIN;清空回收站中对象PURGERECYCLEBIN;还原回收站中的对象FLASHBACKTABLE BINSG0Zl：QZ6GS222JZDCCTFLHQ二二SO TOBEFOREDROPRENAMETOTEST;闪回误删除的表FLASHBACKTABLE AAA TOBEFOREDROP;闪回表中记录到某一时间点ALTERTABLETESTENABLEROWMOVEMEVT;FLASHBACKTABLETESTTOTIHESTAMP TO_TI匡STAMPC 2009-10-15 21:17:47, YYYY-MM-DD HH24:

3、MI:SS，);査看当前会话SELECTSID, SERIAL#, USERNAME, PROGRAM, MACHINE, STATUS FROM VSSESSION;査看DDL锁SELECT*FROM DBA_DDL_LOCKSWHERE OWNER 二FWYANG;检査等待事件SELECTSID, A. USERNAME, EVENT, WAIT.CUSS, Tl. SQL.TEXTFROM V$SESSION A, VSSQLAREA TlWHERE WAIT.CLASS IdleAND A. SQL.ID = Tl.SQL.ID;检査数据文件状态SELECT FILENAML .TUS

4、 FROM DBA.DATA.FILb :检査表空间使用情况SELECTUPPER(F. TABLESPACEN. )D. TOT_GROOTTE_MB ” 衣空用大小(M),D TOT_GROOTTE_MB 一 F. TOTAL.BYTES S使用空间(M),TO_CHAR(ROUND(D. TOT_GROOTTE_MB - F TOTAL.BYTES)/ D. TOT_GROOTTE_MB 100,2),990.99)使用比，F TOTAL_BYTES 空闲乍间(M):FMAX_BYTES 最大块(M)FROM(SELECT TABLESPACENAME,ROUND(SUM(BYTES)/

5、(1024*1024), 2) TOTAL.BYTES,ROUND (MAX (BYTES)/(1024*1024), 2) MAJBYTESFROM SYS.DBA_FREE_SPACEGROUPBY TABLESPACE.NIE) F,(SELECT DD TABLESPACEJCAME.ROUND(SUM(DD. BYTES)/(1024*1024),2) TOT_GROOTTE_MBFROM SYS. DBA_DATA_FILES DDGROUPBY DD. TABLESPACE_AME) DWHERE D. TABLESPACES 二 F TABLESPACE_XAME0RDERBY

6、4DESC;收缩表空间ALTER TABLESPCE TS_AJ_DATA COALESCE;增加表空间大小SELECT T TABLESPACE_AME, T FILEJOME, T. BYTES /1024/1024/1024FROM DBA_DATA_FILES TWHERE T. TABLESPACEJAYE 二TS_AJ_DATA;ALTERTABLESPACE TS_AJ_DATA ADDDATAFILE /DATA/TS_AJ_DATA05_10G DBFSIZE10000MAUTOEXTENDOFF;检查不起作用的约束SELECT OWNER, CONSTR.AINT.NAME

7、, TABLE.NAME, CONSTRAIXTTYPE, STATUS FROM DBACONSTRAINTSWHERE STATUS 二DISABLE;检査发生坏块的数据库对象SELECT TABLESPACE. , SEGMENT_TYPE, OWNER, SEGHET_AMEFROM DBA.EXTENTSWHERE FILE_ID =ANDBETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS -1;检查无效的数据库对象SELECT OWNER, OBJECT.NAME, OBJECT_TYPEFROM DBA_OBJECTSWHERE STATUS 二INVA

8、LID1 ;査看语句执行进度SELECT SE. SID,OPNAME,TRUNC(SOFAR / TOTALWORK *100, 2)丨 |%AS PCT.WORK,ELAPSED.SECONDS ELAPSED,ROUND(ELAPSED.SECONDS *(TOTALWORK - SOFAR)/ SOFAR) REMAIN.TIME, SQL_TEXTFROM V$SESSION_LONGOPS SL, V$SQLAREA SA, VSSESSION SEWHERE SL. SQL_HA：SH_VALUE = SA. HASH.VALUEAND SL.SID = SE. SIDAND S

9、OFAR != TOTALWORKORDERBY START.TIME;检査碎片程度高的表SELECT SEGMET_AME LABLE.NAME, COUNT (*)EXTENTSFROM DBA.SEGMENTSWHERE OWNER NOTIN ( SYS, SYSTEM)GROUPBY SEGMET_NAMEHAVINGCOUNT(*)二(SELECTMAX (COUNT(*)FROM DBA.SEGMENTSGROUPBY SEGMENT.NAME);检査表空间的I/O比例SELECT DF TABLESFACE_ NAME,DF. FILENAMEFILE：F. PHYRDSPYR

10、,F. PHYBLKRDPBR,F. PHYWRTSPYW,F PHYBLKWRTPBWFROM V$FILESTAT F, DBA_DATA_FILES DFWHERE F FILE二二 DF. FILE_IDORDERBY DF. TABLESPACt ;4/9精品好资料学习推荐检查碎片程度高的表SELECT segment_name table_name, COUNT(*)extents FROM dba_segmentsWHERE owner NOTINC SYS, SYSTEM)GROUPBY s egment_nameHAVINGCOUNT(*)二(SELECTMAX(COUNT(

11、*)FROM dba_segmentsGROUPBY segment_name);检査文件系统的I/O比例SELECTSUBSTR (A. FILE#, 1, 2)SUBSTR (A. NAME, 1, 30) Z，NAMEA. STATUS,A. BYTES,B. PHYRDS,B. PHYWRTSFROM V$DATAFILE A, V$FILESTAT BWHERE A. FILE# 二 B. FILE#;检査消耗CPU最高的进程SELECT P.PID PID,S.SID SID,P.SPID SPID,S.USERNAME USERNAME,S. OSUSER OSNAME,P. S

12、ERIAL# S，P. TERMINAL,P. PROGRAM PROGRAM,P.BACKGROUND,S. STATUS,RTRIM(SUBSTR(A. SQL.TEXT, 1, 80) SQLFROMFROM V$PROCESS P, VJSESSION S, VSSQLAREA AWHERE P. ADDR = S. PADDRAND S. SQL_ADDRESS = A. ADDRESS(+)AND P SERIAL# 1 ；检査运行很久的SQLSID,OPNAME,ROUND(SOFAR *100/ TOTALWORK, 0)|%AS PROGRESS,TIMEREMAINING,

13、SQL_TEXTFROM V$SESSION_LONGOPS, V$SQLWHERE TIME.REMAINIXG 0AND SQL_ADDRESS = ADDRESSAND SQL_HASH_VALLE = HASH_VALUE;等待时间最多的5个系统等待事件的获取SELECT*FROM(SELECT*FROM V$SYSTENLEVENTWHERE EVENT NOTLIKE SQL%ORDERBY TOTALWAITS DESC)WHERER0WNfM=5;査找前十条性能差的SQLSELECT*FROM(SELECT ?ARSIXG_USER_ID EXECUTIONS,SORTS,CO

14、WAND_TYPE, DISK_READS, SQL_TEXTFROM V$SQLAREAORDERBY DISK_READS DESC)WHERER0WNVM S/ROW-X (SSX)1,6, EXCLUSIVE1,TO_CHAR (wk. request) mode_requested,TO_CHAR (hk. idl) lock.idl, TO_CHAR (hk. id2) lock_id2,DECODE(hk. BLOCK,8 / 9精品好资料学习推荐0, NOT Blocking, /*/* Abt blocking any other processes */1, ? Block

15、ing，, /*/* This lock blocks other processes */2, * GlobaT , /*/* This lock is global, so we can t tell */TO_CHAR (hk. BLOCK)blocking_othersFROM v$lock hk, v$session bs, v$lock wk, v$session wsWHERE hk. BLOCK =1AND hk. lmode !=0AND hk. lmode !=1AND wk. request !=0AND wk. TYPE (+)二 hk. TYPEAND wk. idl

16、(+)= hk. idlAND wk. id2 (+)= hk. id2AND hk. SID = bs. SID(+)AND wk. SID = ws. SID(+)AND(bs. username ISNOTNULL)AND( js. username SYSTEM)AND(bs. username * SYS*)ORDERBY1;査看正在运行的JOBSELECT*FROM D3A_J0BS_RL、XING;检査数据库JOB的完成情况SELECT JOB, LOG.USER, LAST.DATE, XEXT.DATE, WHAT, FAILURESFROM DBA_JOBS;査看正在运行的JOB对应的SID、SERIAL#. SPIDSELECT A. JOB, B. SID, B. SERIALS, C. SPIDFROM DBA_JOBS_RUNNING A, V$SESSION B, V$PROCESS CWHERE A. SID = B. SID