Translate this Page


In our finance database (Oracle EBS 11.5.9), I found there is a big snapshot log. I found AP.MLOG$_AP_INVOICE_DISTRIBUT has size more than 21 GB and still growing. The size of the tablespace APD is 26 GB, so that snapshot log has consume more than 80% of tablespace APD.

First of all, I analyze which is the master table that used this snapshot log.


SELECT MASTER, log_table, snapshot_id

  FROM dba_snapshot_logs










I found there is no snapshot_id return. I guess that there is no snapshot registered for the master table AP_INVOICE_DISTRIBUTION_ALL. So I can drop the snapshot log AP.MLOG$_AP_INVOICE_DISTRIBUT.


DROP SNAPSHOT LOG ON ap.ap_invoice_distributions_all;


After that to shrink the size of tablespace APD, I create new tablespace APD2, alter user AP default tablespace APD2, and finally move all AP’s tables to tablespace APD2 and rebuild the all AP’s indexes.

 Leave a Reply



You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

© 2012 LevICORP's webnote Suffusion theme by Sayontan Sinha