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

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)",

 

 

 

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) / (10http://www.mianfeiwendang.com/doc/dba9a779825b68483416ac9624 * 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 =

 

AND  BETWEEN BLOCK_ID AND BLOCK_ID   BLOCKS - 1;

 

检查无效的数据库对象 SELECT OWNER,OBJECT_NAME,OBJECT_TYPE

 

FROM DBA_OBJECThttp://www.mianfeiwendang.com/doc/dba9a779825b68483416ac96S

 

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       "FILE",

 

F.PHYRDS           PYR,

 

F.PHYBLKRD         http://www.mianfeiwendang.com/doc/dba9a779825b68483416ac96PBR,

 

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, 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) "NAME",

 

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 PRhttp://www.mianfeiwendang.com/doc/dba9a779825b68483416ac96OGRAM,

 

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,

 

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;

 

等待时间最多的5个系统等待事件的获取

 

SELECT *

 

FROM (SELECT *

 

FROM V$SYSTEM_EVENT

 

WHERE EVENT NOT LIKE 'SQL%'

 

ORDER BY TOTAL_WAITS DESC)

 

WHERE ROWNUM

 

 

 

查找前十条性能差的SQL

 

SELECT *

 

FROM (SELECT PARSING_USER_ID EXECUTIONS,

 

SORTS,

 

COMMAND_TYPE,

 

DISK_READS,://www.mianfeiwendang.com/doc/dba9a779825b68483416ac96r

SQL_TEXT

 

FROM V$SQLAREA

 

ORDER BY DISK_READS DESC)

 

WHERE ROWNUM

 

检查死锁

 

SELECT   bs.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 User",          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',

 

'Ihttp://www.mianfeiwendang.com/doc/dba9a779825b68483416ac96R', 'Instance Recovery',

 

'ST', 'Disk SPACE 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.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,

://www.mianfeiwendang.com/doc/dba9a779825b68483416ac96r0, 'NOT Blocking',         /**//* Not blocking any other processes */

 

1, 'Blocking',             /**//* This lock blocks other processes */

 

2, 'Global',          /**//* This lock is global, so we can't 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 = bs.SID( )

 

AND wk.SID = ws.SID( )

 

AND (bs.username IS NOT NULL)

 

AND (bs.username  'SYSTEM')

 

AND (bs.username  'SYS')

 

ORDER BY 1;

 

 

 

查看正在运行的JOB

 

SELECT * FROM DBA_JOBS_RUNNING;

 

 

 

检查数据库JOB的完成情况

 

SELECT JOB, LOG_USER, LAST_DATE, NEXT_DATE, WHAT, FAILURES  FROM DBA_JOBS;

://www.mianfeiwendang.com/doc/dba9a779825b68483416ac96ar查看正在运行的JOB对应的SID、SERIAL#、SPID

 

SELECT 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#、SPID SELECT 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:23242 ORACLE级别

 

ALTER SYSTEM KILL SESSION '554,1134'; 操作系统级别

 

kill -9 23242