Amount of non-purgeable archive logs for Streams
SQL> SELECT MIN(FIRST_TIME), MAX(FIRST_TIME)
2 FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
3 WHERE r.CONSUMER_NAME = c.CAPTURE_NAME AND
4 r.NEXT_SCN >= c.REQUIRED_CHECKPOINT_SCN AND
5 r.PURGEABLE='NO';MIN(FIRST_TIME) MAX(FIRST_TIME)
-------------------- --------------------
08-MAR-2010 22:01:46 25-MAR-2010 19:36:39
I have checkpoint_retention_time set to 7 and _checkpoint_frequency set to 1000.
Why do I need 17 days' worth of archive logs? When I add a new table to an existing Streams configuration, I see in the alert log that it’s mining all of those past archive logs before it is done. This has been taking around 30 minutes in my development environment. I can't imagine how long it will take when I implement this in production. How can I reduce this?