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

 WHERE log_table = ‘MLOG$_AP_INVOICE_DISTRIBUT’;

 

master

log_table

snapshot_id

AP_INVOICE_DISTRIBUTIONS_ALL

MLOG$_AP_INVOICE_DISTRIBUT

 

 

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

(required)

(required)

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