【Oracle】日常运维手册（中）

查看当前会话

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_OBJECT
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         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, 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 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,
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
复制