【Oracle】Show the change history of tbs' size

注意：

脚本都从dba_hist_tbspc_space_usage系统视图获取数据，但是这个系统视图中保存的数据的时间是依赖AWR采样数据保留期限的。

所以你从这个系统视图可能查找不出很早之前的表空间数据使用情况，如果需要历史的表空间使用数据，可能需要定期采集数据并存储到起来。

In non multitentant DB

-- WX:DBAJOE399 --

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;

In multitentant DB

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;