ORACLE數(shù)據(jù)庫日常維護(hù)手冊(最全+最實(shí)用)

1、精品好資料學(xué)習(xí)推薦ORACLE日常維護(hù)手冊査看數(shù)據(jù)庫版本SELECT*FROM V$VERSION;査看數(shù)據(jù)庫語言環(huán)境SELECTUSERENVC LANGUAGE)FROM DUAL:査看ORACLE實(shí)例狀態(tài)SELECT INSTANCEJAME, HOST_AME, STARTUPIME, STATUS, DATABASE_STATUSFROM V$INSTANCE;査看ORACLE監(jiān)聽狀態(tài)Isnrctl status査看數(shù)據(jù)庫歸檔模式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;閃回表中記錄到某一時(shí)間點(diǎn)ALTERTABLETESTENABLEROWMOVEMEVT;FLASHBACKTABLETESTTOTIHESTAMP TO_TI匡STAMPC 2009-10-15 21:17:47, YYYY-MM-DD HH24:

3、MI:SS，);査看當(dāng)前會話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;檢査數(shù)據(jù)文件狀態(tài)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(xiàn) 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;檢査發(fā)生壞塊的數(shù)據(jù)庫對象SELECT TABLESPACE. , SEGMENT_TYPE, OWNER, SEGHET_AMEFROM DBA.EXTENTSWHERE FILE_ID =ANDBETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS -1;檢查無效的數(shù)據(jù)庫對象SELECT OWNER, OBJECT.NAME, OBJECT_TYPEFROM DBA_OBJECTSWHERE STATUS 二INVA

8、LID1 ;査看語句執(zhí)行進(jìn)度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精品好資料學(xué)習(xí)推薦檢查碎片程度高的表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);檢査文件系統(tǒng)的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最高的進(jìn)程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 ；檢査運(yùn)行很久的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;等待時(shí)間最多的5個(gè)系統(tǒng)等待事件的獲取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精品好資料學(xué)習(xí)推薦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;査看正在運(yùn)行的JOBSELECT*FROM D3A_J0BS_RL、XING;檢査數(shù)據(jù)庫JOB的完成情況SELECT JOB, LOG.USER, LAST.DATE, XEXT.DATE, WHAT, FAILURESFROM DBA_JOBS;査看正在運(yùn)行的JOB對應(yīng)的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