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

1、ORACLE 日常維護(hù)手冊查看數(shù)據(jù)庫版本SELECT * FROM V$VERSION;查看數(shù)據(jù)庫語言環(huán)境SELECT USERENV(LANGUAGE) FROM DUAL;查看ORACLE實(shí)例狀態(tài)SELECT INSTANCE_NAME,HOST_NAME,STARTUP_TIME,STATUS,DATABASE_STATUS FROM V$INSTANCE;查看ORACLE監(jiān)聽狀態(tài)lsnrctl status查看數(shù)據(jù)庫歸檔模式SELECT NAME,LOG_MODE,OPEN_MODE FROM V$DATABASE;查看回收站中對象SELECT OBJECT_NAME,ORIGINAL

2、_NAME,TYPE FROM RECYCLEBIN;清空回收站中對象PURGE RECYCLEBIN;還原回收站中的對象FLASHBACK TABLE BIN$GOZUQZ6GS222JZDCCTFLHQ=$0 TO BEFORE DROP RENAME TO TEST;閃回誤刪除的表FLASHBACK TABLE AAA TO BEFORE DROP;閃回表中記錄到某一時(shí)間點(diǎn)ALTER TABLE TEST ENABLE ROW MOVEMENT;FLASHBACK TABLE TEST TO TIMESTAMP TO_TIMESTAMP(2009-10-15 21:17:47,YYYY-

3、MM-DD HH24:MI:SS);查看當(dāng)前會(huì)話SELECT SID,SERIAL#,USERNAME,PROGRAM,MACHINE,STATUS FROM V$SESSION;查看DDL鎖SELECT * FROM DBA_DDL_LOCKS WHERE OWNER = FWYANG;檢查等待事件SELECT SID, A.USERNAME, EVENT, WAIT_CLASS, T1.SQL_TEXT FROM V$SESSION A, V$SQLAREA T1 WHERE WAIT_CLASS Idle AND A.SQL_ID = T1.SQL_ID;檢查數(shù)據(jù)文件狀態(tài)SELECT F

4、ILE_NAME,STATUS FROM DBA_DATA_FILES;檢查表空間使用情況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 T

5、ABLESPACE_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_N

6、AME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC; 收縮表空間ALTER TABLESPCE TS_AJ_DATA COALESCE;增加表空間大小SELECT T.TABLESPACE_NAME, T.FILE_NAME, T.BYTES / 1024 / 1024 / 1024 FROM DBA_DATA_FILES T WHERE T.TABLESPACE_NAME = TS_AJ_DATA;ALTER TABLESPACE TS_AJ_DATA ADD DATAFILE /DATA/TS_AJ_DATA

7、05_10G.DBF SIZE 10000M AUTOEXTEND OFF;檢查不起作用的約束SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE, STATUS FROM DBA_CONSTRAINTS WHERE STATUS = DISABLE;檢查發(fā)生壞塊的數(shù)據(jù)庫對象SELECT TABLESPACE_NAME, SEGMENT_TYPE, OWNER, SEGMENT_NAME FROM DBA_EXTENTS WHERE FILE_ID = AND BETWEEN BLOCK_ID AND BLOCK_ID + BLO

8、CKS - 1;檢查無效的數(shù)據(jù)庫對象SELECT OWNER,OBJECT_NAME,OBJECT_TYPEFROM DBA_OBJECTSWHERE STATUS =INVALID;查看語句執(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_TEXT FROM V$SESSION_LONGOPS SL,

9、 V$SQLAREA SA, V$SESSION SE WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE AND SL.SID = SE.SID AND SOFAR != TOTALWORK ORDER BY START_TIME;檢查碎片程度高的表SELECT SEGMENT_NAME TABLE_NAME, COUNT(*) EXTENTS FROM DBA_SEGMENTS WHERE OWNER NOT IN (SYS, SYSTEM) GROUP BY SEGMENT_NAMEHAVING COUNT(*) = (SELECT MAX(COUNT(*)

10、FROM DBA_SEGMENTS GROUP BY SEGMENT_NAME);檢查表空間的 I/O 比例SELECT DF.TABLESPACE_NAME NAME, DF.FILE_NAME FILE, F.PHYRDS PYR, F.PHYBLKRD PBR, F.PHYWRTS PYW, F.PHYBLKWRT PBW FROM V$FILESTAT F, DBA_DATA_FILES DF WHERE F.FILE# = DF.FILE_ID ORDER BY DF.TABLESPACE_NAME;檢查碎片程度高的表SELECT segment_name table_name, C

11、OUNT(*) extents FROM dba_segments WHERE owner NOT IN (SYS, SYSTEM) GROUP BY segment_nameHAVING COUNT(*) = (SELECT MAX(COUNT(*) FROM dba_segments GROUP BY segment_name);檢查文件系統(tǒng)的 I/O 比例SELECT SUBSTR(A.FILE#, 1, 2) #, SUBSTR(A.NAME, 1, 30) NAME, A.STATUS, A.BYTES, B.PHYRDS, B.PHYWRTS FROM V$DATAFILE A,

12、V$FILESTAT B WHERE A.FILE# = B.FILE#;檢查消耗CPU最高的進(jìn)程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 FROM V$PROCESS P, V$SESSION S, V$SQLAREA A WHERE P.ADDR = S.P

13、ADDR AND S.SQL_ADDRESS = A.ADDRESS(+) AND P.SERIAL# 1;檢查運(yùn)行很久的SQLSELECT 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;等待時(shí)間最多的5個(gè)系統(tǒng)等待事件的獲取S

14、ELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE SQL% ORDER BY TOTAL_WAITS DESC) 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;檢查死鎖SELECT bs.username Blo

15、cking User, bs.username DB User, ws.username Waiting User, bs.SID SID, ws.SID WSID, bs.serial# Serial#, bs.sql_address address, bs.sql_hash_value Sql hash, gram Blocking App, gram Waiting App, bs.machine Blocking Machine, ws.machine Waiting Machine, bs.osuser Blocking OS User, ws.osuser

16、Waiting OS User, bs.serial# Serial#, ws.serial# WSerial#, DECODE (wk.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 Transactio

17、n, TS, Temp Segment, IV, Library Cache Invalidation, LS, LOG START OR Switch, RW, ROW Wait, SQ, Sequence Number, TE, Extend TABLE, TT, Temp TABLE, wk.TYPE ) lock_type, DECODE (hk.lmode, 0, None, 1, NULL, 2, ROW-S (SS), 3, ROW-X (SX), 4, SHARE, 5, S/ROW-X (SSX), 6, EXCLUSIVE, TO_CHAR (hk.lmode) ) mod

18、e_held, DECODE (wk.request, 0, None, 1, NULL, 2, ROW-S (SS), 3, ROW-X (SX), 4, SHARE, 5, S/ROW-X (SSX), 6, EXCLUSIVE, TO_CHAR (wk.request) ) mode_requested, TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2, DECODE (hk.BLOCK, 0, NOT Blocking, /*/* Not blocking any other processes */ 1, Blocking,

19、/*/* This lock blocks other processes */ 2, Global, /*/* This lock is global, so we cant tell */ TO_CHAR (hk.BLOCK) ) blocking_others FROM v$lock hk, v$session bs, v$lock wk, v$session ws WHERE hk.BLOCK = 1 AND hk.lmode != 0 AND hk.lmode != 1 AND wk.request != 0 AND wk.TYPE(+) = hk.TYPE AND wk.id1(+) = hk.id1 AND wk.id2(+) = hk.id2 AND hk.SID =