Oracle表空间和数据库每日增长大小评估脚本

表空间每日增长量统计

11g

  SELECT a.snap_id,
         c.tablespace_name ts_name,
         TO_CHAR (TO_DATE (a.rtime, 'mm/dd/yyyy hh24:mi:ss'),
                  'yyyy-mm-dd hh24:mi')
            rtime,
         ROUND (a.tablespace_size * c.block_size / 1024 / 1024 / 1024, 2)
            ts_size_gb,
         ROUND (a.tablespace_usedsize * c.block_size / 1024 / 1024 / 1024, 2)
            ts_used_gb,
         ROUND (
              (a.tablespace_size - a.tablespace_usedsize)
            * c.block_size
            / 1024
            / 1024
            / 1024,
            2)
            ts_free_gb,
         ROUND (a.tablespace_usedsize / a.tablespace_size * 100, 2) pct_used
    FROM dba_hist_tbspc_space_usage a,
         (  SELECT tablespace_id,
                   SUBSTR (rtime, 1, 10) rtime,
                   MAX (snap_id) snap_id
              FROM dba_hist_tbspc_space_usage nb
          GROUP BY tablespace_id, SUBSTR (rtime, 1, 10)) b,
         dba_tablespaces c,
         v$tablespace d
   WHERE     a.snap_id = b.snap_id
         AND a.tablespace_id = b.tablespace_id
         AND a.tablespace_id = d.TS#
         AND d.NAME = c.tablespace_name
         AND TO_DATE (a.rtime, 'mm/dd/yyyy hh24:mi:ss') >= SYSDATE - 30
ORDER BY a.tablespace_id, TO_DATE (a.rtime, 'mm/dd/yyyy hh24:mi:ss') DESC;

12c

--针对多租户数据库情况
  SELECT a.snap_id,
         a.con_id,
         e.name pdbname,
         c.tablespace_name ts_name,
         TO_CHAR (TO_DATE (a.rtime, 'mm/dd/yyyy hh24:mi:ss'),
                  'yyyy-mm-dd hh24:mi')
            rtime,
         ROUND (a.tablespace_size * c.block_size / 1024 / 1024 / 1024, 2)
            ts_size_gb,
         ROUND (a.tablespace_usedsize * c.block_size / 1024 / 1024 / 1024, 2)
            ts_used_gb,
         ROUND (
              (a.tablespace_size - a.tablespace_usedsize)
            * c.block_size
            / 1024
            / 1024
            / 1024,
            2)
            ts_free_gb,
         ROUND (a.tablespace_usedsize / a.tablespace_size * 100, 2) pct_used
    FROM cdb_hist_tbspc_space_usage a,
         (  SELECT tablespace_id,
                   nb.con_id,
                   SUBSTR (rtime, 1, 10) rtime,
                   MAX (snap_id) snap_id
              FROM dba_hist_tbspc_space_usage nb
          GROUP BY tablespace_id, nb.con_id, SUBSTR (rtime, 1, 10)) b,
         cdb_tablespaces c,
         v$tablespace d,
         V$CONTAINERS e
   WHERE     a.snap_id = b.snap_id
         AND a.tablespace_id = b.tablespace_id
         AND a.con_id = b.con_id
         AND a.con_id = c.con_id
         AND a.con_id = d.con_id
         AND a.con_id = e.con_id
         AND a.tablespace_id = d.TS#
         AND d.NAME = c.tablespace_name
         AND TO_DATE (a.rtime, 'mm/dd/yyyy hh24:mi:ss') >= SYSDATE - 30
ORDER BY a.CON_ID,
         a.tablespace_id,
         TO_DATE (a.rtime, 'mm/dd/yyyy hh24:mi:ss') DESC;

11g某一个表空间增长量

  SELECT u.snap_id,
         TO_CHAR (s.begin_interval_time, 'yyyy-mm-dd hh24') begin_time,
         TO_CHAR (s.end_interval_time, 'yyyy-mm-dd hh24') end_time,
         t.name,
         ROUND (u.tablespace_size * ts.block_size / 1024 / 1024 / 1024, 2)
            ts_size_gb,
         ROUND (u.tablespace_usedsize * ts.block_size / 1024 / 1024 / 1024, 2)
            ts_used_gb,
         ROUND (
              (u.tablespace_size - u.tablespace_usedsize)
            * ts.block_size
            / 1024
            / 1024
            / 1024,
            2)
            ts_free_gb,
         ROUND (u.tablespace_usedsize / u.tablespace_size * 100, 2) pct_used
    FROM dba_hist_tbspc_space_usage u,
         v$tablespace t,
         dba_hist_snapshot s,
         dba_tablespaces ts
   WHERE     u.tablespace_id = t.ts#
         AND u.snap_id = s.snap_id
         AND t.name = ts.tablespace_name
         AND s.instance_number = 1
         AND t.name = 'SYSTEM'
         AND s.end_interval_time > SYSDATE - 7
ORDER BY snap_id DESC;

数据库每日增长量统计

根据表空间计算

/* 不含UNDO和TEMP表空间 */
WITH tmp
     AS (  SELECT rtime,
                  SUM (tablespace_usedsize_gb) tablespace_usedsize_gb,
                  SUM (tablespace_size_gb) tablespace_size_gb
             FROM (SELECT rtime,
                          e.tablespace_id,
                            (e.tablespace_usedsize)
                          * (f.block_size)
                          / 1024
                          / 1024
                          / 1024
                             tablespace_usedsize_gb,
                            (e.tablespace_size)
                          * (f.block_size)
                          / 1024
                          / 1024
                          / 1024
                             tablespace_size_gb
                     FROM dba_hist_tbspc_space_usage e,
                          dba_tablespaces f,
                          v$tablespace g
                    WHERE     e.tablespace_id = g.TS#
                          AND f.tablespace_name = g.NAME
                          AND f.contents NOT IN ('TEMPORARY', 'UNDO'))
         GROUP BY rtime)
SELECT tmp.rtime,
       tablespace_usedsize_gb,
       tablespace_size_gb,
       (tablespace_usedsize_gb
        - LAG (tablespace_usedsize_gb, 1, NULL) OVER (ORDER BY tmp.rtime))
          AS DIFF_GB
  FROM tmp,
       (  SELECT MAX (rtime) rtime
            FROM tmp
        GROUP BY SUBSTR (rtime, 1, 10)) t2
 WHERE t2.rtime = tmp.rtime;

/* 含UNDO和TEMP表空间 */
WITH tmp
     AS (  SELECT MIN (rtime) rtime,
                  SUM (tablespace_usedsize_gb) tablespace_usedsize_gb,
                  SUM (tablespace_size_gb) tablespace_size_gb
             FROM (SELECT rtime,
                          e.tablespace_id,
                            (e.tablespace_usedsize)
                          * (f.block_size)
                          / 1024
                          / 1024
                          / 1024
                             tablespace_usedsize_gb,
                            (e.tablespace_size)
                          * (f.block_size)
                          / 1024
                          / 1024
                          / 1024
                             tablespace_size_gb
                     FROM dba_hist_tbspc_space_usage e,
                          dba_tablespaces f,
                          v$tablespace g
                    WHERE e.tablespace_id = g.TS#
                          AND f.tablespace_name = g.NAME)
         GROUP BY rtime)
SELECT tmp.rtime,
       tablespace_usedsize_gb,
       tablespace_size_gb,
       (tablespace_usedsize_gb
        - LAG (tablespace_usedsize_gb, 1, NULL) OVER (ORDER BY tmp.rtime))
          AS DIFF_KB
  FROM tmp,
       (  SELECT MIN (rtime) rtime
            FROM tmp
        GROUP BY SUBSTR (rtime, 1, 10)) t2
 WHERE t2.rtime = tmp.rtime