ORACLE 数据库运维巡检
SQL执行次数
SELECT T.* FROM (
SELECT M.SQL_ID,M.INSTANCE_NUMBER 数据库实例,
S.SQL_TEXT 语句,
TO_CHAR(N.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI') 时间,
SUM(M.EXECUTIONS_DELTA) AS EXCUTE_TIMES
FROM DBA_HIST_SQLSTAT M, DBA_HIST_SNAPSHOT N, V$SQLAREA S
WHERE M.SNAP_ID = N.SNAP_ID
AND M.DBID = N.DBID
AND M.INSTANCE_NUMBER = N.INSTANCE_NUMBER
AND S.SQL_ID = M.SQL_ID
AND TO_CHAR(N.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI') >=TO_CHAR(SYSDATE -1,'YYYY-MM-DD HH24:MI')
--AND M.SQL_ID = 'DN2N99665YJ4N'
GROUP BY M.SQL_ID,M.INSTANCE_NUMBER,
S.SQL_TEXT,
TO_CHAR(N.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI')
ORDER BY SUM(M.EXECUTIONS_DELTA)ASC,TO_CHAR(N.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI') DESC
) T
WHERE T.EXCUTE_TIMES >=10000;
表空间使用情况
SELECT
A.TABLESPACE_NAME "表空间名",
C.STATUS "联机状态",
TOTAL "表空间大小",
FREE "表空间剩余大小",
(TOTAL - FREE) "表空间使用大小",
TOTAL / (1024 * 1024 * 1024) "表空间大小(G)",
FREE / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(TOTAL - FREE) / (1024 * 1024 * 1024) "表空间使用大小(G)",
ROUND((TOTAL - FREE) / TOTAL, 4) * 100 "使用率 %"
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) B,
DBA_TABLESPACES C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND C.TABLESPACE_NAME = A.TABLESPACE_NAME;
查看表大小
SELECT SEGMENT_NAME,SUM(BYTES)/1024/1024
FROM USER_EXTENTS
WHERE SEGMENT_TYPE = 'TABLE'
GROUP BY SEGMENT_NAME ORDER BY SUM(BYTES) DESC;
查看表大小
SELECT SEGMENT_NAME,
BYTES/1024/1024 FROM USER_SEGMENTS
WHERE SEGMENT_TYPE = 'TABLE'
ORDER BY BYTES DESC;