注意：
脚本都从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;