《ORACLE数据库日常维护手册(最全+最实用)(共9页).doc》由会员分享，可在线阅读，更多相关《ORACLE数据库日常维护手册(最全+最实用)(共9页).doc（9页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、精选优质文档-倾情为你奉上ORACLE 日常维护手册查看数据库版本SELECT * FROM V$VERSION;查看数据库语言环境SELECT USERENV(LANGUAGE) FROM DUAL;查看ORACLE实例状态SELECT INSTANCE_NAME,HOST_NAME,STARTUP_TIME,STATUS,DATABASE_STATUS FROM V$INSTANCE;查看ORACLE监听状态lsnrctl status查看数据库归档模式SELECT NAME,LOG_MODE,OPEN_MODE FROM V$DATABASE;查看回收站中对象SELECT OBJECT_
2、NAME,ORIGINAL_NAME,TYPE FROM RECYCLEBIN;清空回收站中对象PURGE RECYCLEBIN;还原回收站中的对象FLASHBACK TABLE BIN$GOZUQZ6GS222JZDCCTFLHQ=$0 TO BEFORE DROP RENAME TO TEST;闪回误删除的表FLASHBACK TABLE AAA TO BEFORE DROP;闪回表中记录到某一时间点ALTER TABLE TEST ENABLE ROW MOVEMENT;FLASHBACK TABLE TEST TO TIMESTAMP TO_TIMESTAMP(2009-10-15 2
3、1:17:47,YYYY-MM-DD HH24:MI:SS);查看当前会话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;检查数
4、据文件状态SELECT FILE_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) F
5、ROM (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
6、.TABLESPACE_NAME) 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 /DA
7、TA/TS_AJ_DATA05_10G.DBF SIZE 10000M AUTOEXTEND OFF;检查不起作用的约束SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE, STATUS FROM DBA_CONSTRAINTS WHERE STATUS = DISABLE;检查发生坏块的数据库对象SELECT TABLESPACE_NAME, SEGMENT_TYPE, OWNER, SEGMENT_NAME FROM DBA_EXTENTS WHERE FILE_ID = AND BETWEEN BLOCK_ID AND B
8、LOCK_ID + BLOCKS - 1;检查无效的数据库对象SELECT OWNER,OBJECT_NAME,OBJECT_TYPEFROM DBA_OBJECTSWHERE STATUS =INVALID;查看语句执行进度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$SESSIO
9、N_LONGOPS SL, 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
10、MAX(COUNT(*) 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
11、table_name, COUNT(*) 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);检查文件系统的 I/O 比例SELECT SUBSTR(A.FILE#, 1, 2) #, SUBSTR(A.NAME, 1, 30) NAME, A.STATUS, A.BYTES, B.PHYRDS, B.PHYWRTS FROM V
12、$DATAFILE A, V$FILESTAT B WHERE A.FILE# = B.FILE#;检查消耗CPU最高的进程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
13、 P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS(+) AND P.SERIAL# 1;检查运行很久的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;等待时间最多
14、的5个系统等待事件的获取SELECT * 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
15、.username Blocking 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, bs.program Blocking App, ws.program Waiting App, bs.machine Blocking Machine, ws.machine Waiting Machine, bs.osuser Blocking OS Use
16、r, ws.osuser 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 SPA
17、CE Transaction, 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
18、.lmode) ) mode_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 */
19、1, Blocking, /*/* 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
20、 AND wk.id1(+) = hk.id1 AND wk.id2(+) = hk.id2 AND hk.SID = bs.SID(+) AND wk.SID = ws.SID(+) AND (bs.username IS NOT NULL) AND (bs.username SYSTEM) AND (bs.username SYS)ORDER BY 1;查看正在运行的JOBSELECT * FROM DBA_JOBS_RUNNING;检查数据库JOB的完成情况SELECT JOB, LOG_USER, LAST_DATE, NEXT_DATE, WHAT, FAILURES FROM DB
21、A_JOBS;查看正在运行的JOB对应的SID、SERIAL#、SPIDSELECT A.JOB, B.SID, B.SERIAL#, C.SPID FROM DBA_JOBS_RUNNING A, V$SESSION B, V$PROCESS C WHERE A.SID = B.SID AND B.PADDR = C.ADDR AND JOB = 21;通过SID查询SERIAL#、SPIDSELECT B.SID, B.SERIAL#, C.SPID FROM V$SESSION B, V$PROCESS C WHERE B.SID = 554 AND B.PADDR = C.ADDR;停止会话-SID:554 SERIAL#:1134 SPID:23242ORACLE级别ALTER SYSTEM KILL SESSION 554,1134;操作系统级别kill -9 23242专心-专注-专业