【ORACLE表空间增长过快问题深入排查】

短命女 2023-10-02 21:14 11阅读 0赞

## 1、用脚本1查询出最近10天增长最快的表空间及索引 ##

### 脚本1：Oracle查询近N天表空间对象增长情况 ###

SET LINES 200 PAGES 200
    COL OWNER FOR A10
    WITH T1 AS
     (SELECT TO_CHAR(B.BEGIN_INTERVAL_TIME, 'YYYYMMDD') SNAPDATE,
             A.TS#,
             A.OBJ#,
             TRUNC(SUM(A.SPACE_ALLOCATED_DELTA) / 1024 / 1024) DELTA_MB
        FROM DBA_HIST_SEG_STAT A, DBA_HIST_SNAPSHOT B
       WHERE A.SNAP_ID = B.SNAP_ID
         AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER
         AND B.SNAP_ID >
             (SELECT MIN(SNAP_ID)
                FROM DBA_HIST_SNAPSHOT
               WHERE BEGIN_INTERVAL_TIME > SYSDATE - &DAYS)
      --AND A.SPACE_ALLOCATED_DELTA > 1024 * 1024 * 10 -- LIMIT  SIZE  MB
       GROUP BY TO_CHAR(B.BEGIN_INTERVAL_TIME, 'YYYYMMDD'), A.TS#, A.OBJ#
       ORDER BY TO_CHAR(B.BEGIN_INTERVAL_TIME, 'YYYYMMDD'))
    SELECT A.SNAPDATE,
           A.TS#,
           --A.OBJ#,
           B.OBJECT_NAME,
           B.OWNER,
           SUM(A.DELTA_MB) DELTA_MB
      FROM T1 A, DBA_HIST_SEG_STAT_OBJ B
     WHERE A.OBJ# = B.OBJ#
       AND A.TS# =
           (SELECT TS# FROM V$TABLESPACE WHERE NAME = UPPER('&TABLESPACE_NAME'))
     GROUP BY A.SNAPDATE,
              A.TS#,
              --A.OBJ#,
              B.OBJECT_NAME,
              B.OWNER
     HAVING SUM (A.DELTA_MB) >= &SUMDELTASIZE
     ORDER BY A.SNAPDATE;

### 结果输出如下：（最近10天，表空间名为HIS\_DATA，大于1000M表空间） ###

### ![18f0813b6f6246e5b65e41fc3176bf1c.png][] ###

2、

##  ##

[18f0813b6f6246e5b65e41fc3176bf1c.png]: https://img-blog.csdnimg.cn/18f0813b6f6246e5b65e41fc3176bf1c.png