Show table/schema size difference in time

Here's how to display the size evolution of a table or schema between its current state, and its state some time before. The distance you can go back in time depends of course on the flashback setting of your oracle instance.

When executed, those SQL commands will ask you for a date (past time) and a schema name. For the table version, it will output the list of tables of the given schema. DBA rights are required (access to dba_segments table).

List of table sizes for a schema, compared between now and the given past date :

select ds.tablespace_name,
   ds.owner,
   ds.segment_name table_name,   
   trunc(sum(ods.bytes)/(1024*1024)) previous_size_Mo,
   trunc(sum(ds.bytes)/(1024*1024)) current_size_Mo
from dba_segments ds
inner join dba_segments as of timestamp to_timestamp('&date','YYYY-MM-DD HH:MI:SS') ods
 on ods.owner=ds.owner
 and ods.segment_type=ds.segment_type
 and ods.segment_name=ds.segment_name
where ds.segment_type='TABLE'
and ds.owner=uper('&owner')
group by ds.tablespace_name, ds.owner, ds.segment_name
having sum(ds.bytes)/(1024*1024) > 5
order by current_size_Mo desc;


Schema size difference for the given owner between now and the given past date :

select ds.tablespace_name,
    sum(os.bytes)/1024/1024 AS previous_total_size_Mo,
     sum(ds.bytes)/1024/1024 AS current_total_size_Mo
from dba_segments ds
inner join dba_segments as of timestamp to_timestamp('&date','YYYY-MM-DD HH:MI:SS') os
 on os.owner=ds.owner
 and os.segment_type=ds.segment_type
 and os.segment_name=ds.Segment_name
where ds.owner = upper('&owner')
group by ds.tablespace_name