TRUNCATING A SNAPSHOT LOG.

If a snapshot log grows and allocates many extents, purging the log of rows
does not reduce the amount of space allocated for the log. To reduce the space
allocated for a snapshot log:

1. Acquire an exclusive lock on the master table to prevent updates from occurring
during the following process. For example, issue a statement similar to the following:

     LOCK TABLE scott.emp IN EXCLUSIVE MODE;

2. Using a second database session, copy the rows in the snapshot log (in other words,
the MLOG$ base table) to a temporary table. For example, issue a statement similar
to the following:

     CREATE TABLE scott.templog AS SELECT * FROM scott.mlog$_emp;

3. Using the second session, truncate the log using the SQL statement TRUNCATE.
For example, issue a statement similar to the following:

     TRUNCATE TABLE scott.mlog$_emp;

4. Using the second session, reinsert the old rows so that you do not have to perform.
a complete refresh of the dependent snapshots. For example, issue a statement similar
to the following:

     INSERT INTO scott.mlog$_emp SELECT * FROM scott.templog;
     DROP TABLE scott.templog;

5.Using the first session, release the exclusive lock on the master table by performing
a rollback:

     ROLLBACK;

Note: Any changes made to the master table between the time you copy the rows to a new
location and when you truncate the log  do not appear until after you perform. a complete
refresh. Then it’s better to truncate the snapshot log when it is empty.
Only the owner of a snapshot log or a user with the DELETE ANY TABLE system privilege can
truncate a snapshot log.