I have a problem at the moment where my snapshot logs are not being purged.
Is there an easy way to purge the transactions that HAVE been applied to my replicated database?
I have looked at dbms_snapshot, but never used it!
Will any of these procedures or others purge safely?
Thanks in advance.
directly from Oracle documentation :
Purging Rows from a Snapshot Log
Always try to keep a snapshot log as small as possible to minimize the database space that it uses. To remove rows from a snapshot log and make space for newer log records, you can:
Refresh the snapshots associated with the log so that Oracle can purge rows from the snapshot log.
Manually purge records in the log by deleting rows required only by the nth least recently refreshed snapshots.
To manually purge rows from a snapshot log, execute the PURGE_LOG stored procedure of the DBMS_SNAPSHOT package at the database that contains the log. For example, to purge entries from the snapshot log of the CUSTOMERS table that are necessary only for the least recently refreshed snapshot, execute the following procedure:
master => 'sales.customers',
num => 1,
flag => 'DELETE');
Additional Information: See the DBMS_SNAPSHOT. PURGE_LOG procedure in the Oracle8i Replication API Reference book for details.
Does this mean that I will not be losing transaction information that has not yet been replicated?
ie. I will not lose any data by doing this?
the point is that you choose : you can either refresh your snapshot and let Oracle clean the snapshot log, OR you can purge manually.
It seems that what you want is :
exec dbms_snapshot.refresh('name of your snapshot','C');
this will make a complete refresh of your snapshot
I am not sure what's your problem. when you implement snapshot, you should shedule a purge job using dbms_defer_sys.purge to clean up snapshot log.
it supposes to verfiy all snapshots been updated then do the purge. my environment has 2 snapshots so far it works pretty "clean".