How to retrieve growth history for oracle tablespaces

Name Null? Type
 ------------------------------ -------- --------------------------------------------
 SNAP_ID NUMBER
 DBID NOT NULL NUMBER
 TABLESPACE_ID NUMBER
 TABLESPACE_SIZE NUMBER
 TABLESPACE_MAXSIZE NUMBER
 TABLESPACE_USEDSIZE NUMBER
 RTIME VARCHAR2(25)
 CON_DBID NUMBER
 CON_ID NUMBER
 

col ts_mb for 999,999,999,999.90
 col max_mb for 999,999,999,999.90
 col used_mb for 999,999,999,999.90
 col last_mb for 999,999,999,999.90
 col incr for 999,999.90
 
  select * from (
  select 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.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 - 1/12
  /* For a specific tablespace */
  and v.ts# = 1
 order by v.name, h.snap_id asc)
  where incr > 0;
 

NAME TS# INSTANCE_NUMBER TS_MB MAX_MB USED_MB RESIZE_TIME LAST INCR
 ---------- ----- --------------- ------------------- ------------------- ------------------- ------------------- ------------------- -----------
 SYSAUX 1 2 93,852.00 141,319.94 17,182.13 2019-02-25 13:15:32 17,181.06 1.06
 SYSAUX 1 1 93,852.00 141,319.94 17,186.13 2019-02-25 13:30:38 17,182.13 4.00
 SYSAUX 1 2 93,852.00 141,319.94 17,187.13 2019-02-25 13:45:43 17,186.13 1.00
 SYSAUX 1 1 93,852.00 141,319.94 17,189.13 2019-02-25 14:00:47 17,187.13 2.00
 
 4 rows selected.
 

break on resized
  with ts_history as (
  select * from (
  select 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
 , (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
 order by v.name, h.snap_id asc)
  where incr > 0)
  select to_char(resize_time, 'YYYY-MM') as resized
 , name
 , sum(incr) incr
  from ts_history
 group by name
 , to_char(resize_time, 'YYYY-MM')
 order by 1, 3 desc;
 

RESIZED NAME INCR
 ------- ------------------------------ -----------
 2019-02 INDEXES 21,383.50
  DATA 12,649.00
  SYSAUX 529.63
  USERS 1.06
  SYSTEM 1.00
 
 5 rows selected.