I try to automate deleting logs from Oracle Downstream target database, which are not needed for capture process anymore. The query:


returns, for example, 10 archive logs. Is it correct to delete older logs, not registered in capture process? Please help to find correct suggestion. Is there any ready solutions for dealing with the old redo-transported logs or I need to write script from scratch?


spool the below query output and put into the script. This will delete the logs which are not required anymore.

SELECT -- r.CONSUMER_NAME, 'rm ' || r.NAME -- , to_char(r.FIRST_SCN), -- to_char(r.NEXT_SCN), -- case when (r.next_scn > c.required_checkpoint_scn) then 'NO' else 'YES' end purgable FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c WHERE r.CONSUMER_NAME = c.CAPTURE_NAME and r.source_database = c.source_database and r.next_scn < ( select min(required_checkpoint_scn) from dba_capture where capture_type='DOWNSTREAM') and purgeable='YES' order by modified_time;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.