Sometimes we need to enable flashback for x number of days. In such case, we need to estimate the flashback space required for x number of days in order to store the flashback logs. Note : The flashback log size is same as archive log size generated in a database. - Check the archive generation size via below query - Check the archive generation size via below query - Check the archive generation size via below query - Take the average per day size of archives generated - Multiply the average archive size with x number of days - Then add the required space for flashback file system.

1
2
3
4
5
6

select to_char(COMPLETION_TIME,'DD-MON-YYYY') Arch_Date,count(*) No#_Logs,
sum((BLOCKS*512)/1024/1024/1024) Arch_LogSize_GB
from v$archived_log
where to_char(COMPLETION_TIME,'DD-MON-YYYY')>=trunc(sysdate-7) and DEST_ID=1
group by to_char(COMPLETION_TIME,'DD-MON-YYYY')
order by to_char(COMPLETION_TIME,'DD-MON-YYYY');