Oracle table data comparison through time

Another flashback tips. This one allows you to compare data from your tables through time. Keep in mind the flashback setting of your oracle instance defines how far back in time you can go.

The key here is to perform an inner join between the table and its flashback counterpart. Here's how :

select current.value, past.valuefrom my_table current

inner join my_table as of timestamp sysdate-1 past
on current.id=past.id
where current.blah > 0

This query compares table my_table between its current state and its state one day ago (sysdate-1). Of course, one can use as of timestamp to_timestamp(....) to set a more specific time. Inner join is performed on the id field (if there's one :) ), and i added a filter (the where part), but this is for the show. The core of the functionality is the inner join w/ as of timestamp part.  

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