That depends:
Restore Points are nothing more than pointers to flashback logs in the FRA. If you are creating these pointers via:create restore point <rpname>
, then you can do something like:
begin
for rp in (SELECT name FROM V$RESTORE_POINT where restore_point_time < sysdate - 30)
loop
execute immediate 'drop restore point ' || rp.name;
end loop;
end;
However, if you are not explictly creating these restore points, and you are just concerned about space consumption and reclamation, then take a look at the FRA:
show parameter db_flashback_retention_target;
show parameter db_recovery_file_dest;
show parameter db_recovery_file_dest_size;
The relationship between these parameters is as follows:
The flashback logs will be stored in db_recovery_file_dest.
db_flashback_retention_target is how long (in minutes) you want to keep these flashback logs for.
However, the logs will only consume space until the size specified in db_recovery_file_dest_size is reached.
You can estimate how large to make db_recovery_file_dest_size with the following query:
SELECT ESTIMATED_FLASHBACK_SIZE/1024/1024/1024 --in gigabytes
FROM V$FLASHBACK_DATABASE_LOG;
Therefore, if you want to keep flashback logs for 30 days, you would:
alter system set db_flashback_retention_target = 43200 scope=both;
--43200 = 1440 minutes in a day times 30
After some amount of time, check the estimated_flash_back_size in v$flashback_database_log, and size db_recovery_file_dest_size appropriately. The flashback logs will be cleaned up automatically without the need for any sort of cleanup script. When sizing the FRA, keep in mind that you may be storing other things in the FRA (like RMAN backups), which you can check with:
select * from V$FLASH_RECOVERY_AREA_USAGE;
You can read up on restore points, flashback database, and the FRA from the Oracle docs here.