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 "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 21: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;
检查数据文件状态
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)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2<