Oracle获取数据量增长的表空间信息脚本

在Oracle数据库中，我们有时候在分析一些问题时，需要了解哪一些表空间的数据增长了。我们需要快速定位数据量增长较快的用户表空间，或者在哪一些时间段表空间数据量突然飚增了。下面这个脚本可以快速获取那些数据使用量增长的表空间信息。

set linesize 860;
set pagesize 120;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
col tb_name for a16;
col ts_mb for 999,999,999.90
col max_mb for 999,999,999.90
col used_mb for 999,999,999.90
col last_mb for 999,999,999.90
col incr for 999,999.90


select * from (
  select v.name                         tb_name
        ,v.ts#
        ,s.instance_number
        ,h.tablespace_size
       * round(p.value/1024/1024,2)     ts_mb
        ,h.tablespace_maxsize
       * round(p.value/1024/1024,2)     max_mb
        ,h.tablespace_usedsize
       * round(p.value/1024/1024,2)     used_mb
        ,to_date(h.rtime, 'MM/DD/YYYY HH24:MI:SS') resize_time
        ,lag(h.tablespace_usedsize * p.value/1024/1024, 1, h.tablespace_usedsize * p.value/1024/1024)
         over (partition by v.ts# order by h.snap_id) last_mb
        ,(h.tablespace_usedsize * p.value/1024/1024)
       - lag(h.tablespace_usedsize * p.value/1024/1024, 1, h.tablespace_usedsize * p.value/1024/1024)
         over (partition by v.ts# order by h.snap_id) incr
    from dba_hist_tbspc_space_usage     h
       , dba_hist_snapshot              s
       , v$tablespace                   v
       , dba_tablespaces                t
       , v$parameter                    p
   where h.tablespace_id                = v.ts#
     and v.name                         = t.tablespace_name
     and t.contents                not in ('UNDO', 'TEMPORARY')
     and p.name                         = 'db_block_size'
     and h.snap_id                      = s.snap_id
         /* For a specific time */
     and s.begin_interval_time          > sysdate - 7
order by v.name, h.snap_id asc)
   where incr > 0;

如果要查看某个特定表空间的数据使用量是否增长，可以增加一个过滤条件，如下所示：

set linesize 860;
set pagesize 120;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
col tb_name for a16;
col ts_mb for 999,999,999.90
col max_mb for 999,999,999.90
col used_mb for 999,999,999.90
col last_mb for 999,999,999.90
col incr for 999,999.90


select * from (
  select v.name                        tb_name
        ,v.ts#
        ,s.instance_number
        ,h.tablespace_size
       * round(p.value/1024/1024,2)     ts_mb
        ,h.tablespace_maxsize
       * round(p.value/1024/1024,2)     max_mb
        ,h.tablespace_usedsize
       * round(p.value/1024/1024,2)     used_mb
        ,to_date(h.rtime, 'MM/DD/YYYY HH24:MI:SS') resize_time
        ,lag(h.tablespace_usedsize * p.value/1024/1024, 1, h.tablespace_usedsize * p.value/1024/1024)
         over (partition by v.ts# order by h.snap_id) last_mb
        ,(h.tablespace_usedsize * p.value/1024/1024)
       - lag(h.tablespace_usedsize * p.value/1024/1024, 1, h.tablespace_usedsize * p.value/1024/1024)
         over (partition by v.ts# order by h.snap_id) incr
    from dba_hist_tbspc_space_usage     h
       , dba_hist_snapshot              s
       , v$tablespace                   v
       , dba_tablespaces                t
       , v$parameter                    p
   where h.tablespace_id                = v.ts#
     and v.name                         = t.tablespace_name
     and t.contents                not in ('UNDO', 'TEMPORARY')
     and p.name                         = 'db_block_size'
     and h.snap_id                      = s.snap_id
         /* For a specific time */
     and s.begin_interval_time          > sysdate - 7
         /* For a specific tablespace */
     and v.name =upper('&tablespace_name')
order by v.name, h.snap_id asc)
   where incr > 0;

例如，我们查询表空间SYSAUX的增长情况，如下截图所示

这个脚本适用于Oracle 10/11g,但是在Oracle 12c等多租户环境下，只能查看当前PDB的的表空间，如果想要在CDB下查看所有PDB数据库的表空间增长信息的话，我对这个脚本进行了简单的修改。个人在12c/19c等多个环境下测试均没有问题

--注意：此脚本要在CDB下执行才能查看所有PDB的表空间信息。如果在指定的PDB下执行的，只能查看当前PDB的表空间增长信息
set linesize 860;
set pagesize 120;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
col con_id for 999
col name for a16
col ts# for 999
col ts_mb for 999,999,999.90
col max_mb for 999,999,999.90
col used_mb for 999,999,999.90
col last_mb for 999,999,999.90
col incr for 999,999.90


select * from (
  select v.con_id
        ,v.name
        ,v.ts#
        ,s.instance_number
        ,h.tablespace_size
       * p.value/1024/1024              ts_mb
        ,h.tablespace_maxsize
       * p.value/1024/1024              max_mb
        ,h.tablespace_usedsize
       * p.value/1024/1024              used_mb
        ,to_date(h.rtime, 'MM/DD/YYYY HH24:MI:SS') resize_time
        ,lag(h.tablespace_usedsize * p.value/1024/1024, 1, h.tablespace_usedsize * p.value/1024/1024)
         over (partition by v.ts# order by h.snap_id) last_mb
        ,(h.tablespace_usedsize * p.value/1024/1024)
       - lag(h.tablespace_usedsize * p.value/1024/1024, 1, h.tablespace_usedsize * p.value/1024/1024)
         over (partition by v.con_id, v.ts# order by h.snap_id) incr
    from dba_hist_tbspc_space_usage     h
       , dba_hist_snapshot              s
       , v$tablespace                   v
       , cdb_tablespaces                t
       , v$parameter                    p
   where h.tablespace_id                = v.ts#
     and h.con_id                       = v.con_id
     and h.con_id                       = t.con_id
     and v.name                         = t.tablespace_name
     and v.con_id                       = t.con_id
     and t.contents                not in ('UNDO', 'TEMPORARY')
     and p.name                         = 'db_block_size'
     and h.snap_id                      = s.snap_id
     /* For a specific time */
     and s.begin_interval_time          > sysdate - 7
order by v.con_id, v.name, h.snap_id asc)
   where incr > 0;

查看指定表空间的增长情况

  
set linesize 860;
set pagesize 120;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
col con_id for 999
col name for a16
col ts# for 999
col ts_mb for 999,999,999.90
col max_mb for 999,999,999.90
col used_mb for 999,999,999.90
col last_mb for 999,999,999.90
col incr for 999,999.90


select * from (
  select v.con_id
        ,v.name
        ,v.ts#
        ,s.instance_number
        ,h.tablespace_size
       * p.value/1024/1024              ts_mb
        ,h.tablespace_maxsize
       * p.value/1024/1024              max_mb
        ,h.tablespace_usedsize
       * p.value/1024/1024              used_mb
        ,to_date(h.rtime, 'MM/DD/YYYY HH24:MI:SS') resize_time
        ,lag(h.tablespace_usedsize * p.value/1024/1024, 1, h.tablespace_usedsize * p.value/1024/1024)
         over (partition by v.ts# order by h.snap_id) last_mb
        ,(h.tablespace_usedsize * p.value/1024/1024)
       - lag(h.tablespace_usedsize * p.value/1024/1024, 1, h.tablespace_usedsize * p.value/1024/1024)
         over (partition by v.con_id, v.ts# order by h.snap_id) incr
    from dba_hist_tbspc_space_usage     h
       , dba_hist_snapshot              s
       , v$tablespace                   v
       , cdb_tablespaces                t
       , v$parameter                    p
   where h.tablespace_id                = v.ts#
     --and h.con_id                       = s.con_id
     and h.con_id                       = v.con_id
     and h.con_id                       = t.con_id
     and v.name                         = t.tablespace_name
     and v.con_id                       = t.con_id
     and t.contents                not in ('UNDO', 'TEMPORARY')
     and p.name                         = 'db_block_size'
     and h.snap_id                      = s.snap_id
         /* For a specific time */
     and s.begin_interval_time          > sysdate - 7
         /* For a specific tablespace */
     and v.name =upper('&tablespace_name')
order by v.con_id, v.name, h.snap_id asc)
   where incr > 0;