BY FWYANG
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_NAME,ORIGINAL_NAME,TYPE FROM RECYCLEBIN;
清空回收站中对象
PURGE RECYCLEBIN;
还原回收站中的对象
FLASHBACK TABLE \TO BEFORE DROP RENAME TO TEST;
1
BY FWYANG
闪回误删除的表
FLASHBACK TABLE AAA TO BEFORE DROP;
闪回表中记录到某一时间点
ALTER TABLE TEST ENABLE ROW MOVEMENT; FLASHBACK TABLE TEST TO TIMESTAMP 21:17:47','YYYY-MM-DD HH24:MI:SS');
TO_TIMESTAMP('2009-10-15
查看当前会话
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;
检查数据文件状态
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)\,
2
BY FWYANG
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, (SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.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 '/DATA/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 = < AFN >
AND < BLOCK > BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
3
BY FWYANG
检查无效的数据库对象
SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS
WHERE 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$SESSION_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_NAME
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM DBA_SEGMENTS
GROUP BY SEGMENT_NAME);
检查表空间的 I/O 比例
SELECT DF.TABLESPACE_NAME NAME, DF.FILE_NAME \, 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;
4
BY FWYANG
检查碎片程度高的表
SELECT segment_name table_name, COUNT(*) extents FROM dba_segments
WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM dba_segments
GROUP BY segment_name);
检查文件系统的 I/O 比例
SELECT SUBSTR(A.FILE#, 1, 2) \, SUBSTR(A.NAME, 1, 30) \, A.STATUS, A.BYTES, B.PHYRDS, B.PHYWRTS
FROM V$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 P.ADDR = S.PADDR
AND S.SQL_ADDRESS = A.ADDRESS(+) AND P.SERIAL# <> '1';
检查运行很久的SQL
SELECT USERNAME,
5