oracle 数据库运维巡检

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;