Tuesday Jan 20, 2015

Demantra Large Table Partitions and Using the Flashback Recycle bin, recyclebin, dba_recyclebin and sys.RECYCLEBIN$ Purge Best Practice

This is covered using MOS note:

Demantra Large Table Partitions and Using the Flashback Recycle bin, recyclebin, dba_recyclebin and sys.RECYCLEBIN$ Purge Best Practice (Doc ID 1962730.1)

When you are using the  feature and Oracle partitions are involved you will need to perform additional due diligence.  After the automatic
purge that occurrs when the quota is reached or after you issue a purge command to the recyclebin, you will notice that there are orphaned
BIN$ objects that consume the same space as the original partition that was dropped and the purged.

 

This was the solution based off of a customer SR that puzzled us at first.  The flashback documentation does not discuss the flashback purge
and partitions.  We recommend following the current best practice when managing the flashback feature and partitions.

If you have already performed or if the auto purge has occured, you will need to perform the following.  Of course customized to you BIN$ object name:

ALTER SESSION SET RECYCLEBIN=OFF;
drop table lpudemantra."BIN$A3+yI1NBASrgUwoVBkIBKg==$0";
drop table lpudemantra."BIN$A4gGznhiAVbgUwoVBkIBVg==$0";
.
.
.
.
drop table lpudemantra."BIN$9WCVYRl3BGbgQwoVBkIEZg==$0";

If you have not experienced the purge of the recycle bin, attempt the following:

  1) select count(*) from sys.recyclebin$;

  2) Instead of simply trying to purge the table we can use the following alternative:

     a) flashback table <table_name> to before drop;

          or

          if  <table_name> is currently used by another object:

        flashback table <table_name> to before drop rename to <new_table_name>;


     b) create a script that will drop all the partitions one by one :

        spool drop_<table_name>_partitions.sql

        select 'alter table '|| table_owner|| '.'|| table_name ||' drop partition '|| partition_name||';'
        from dba_tab_partitions
        where table_name='<table_name>'
        /

        spool off


     c) run drop_<table_name>_partitions.sql


     d) drop table <table_name>;


     e) purge recyclebin;


To turn off the recyclebin
  - alter system set recyclebin=off scope=spfile;

Tuesday Aug 14, 2012

Purging History for Net Change and Complete Refresh Collections

Data Load Real Time Customer Question:

When we run Shipment and Booking History Download, the date range of the data profile Purge History Data is updated at run time with
the date range chosen while running the concurrent program, Shipment and Booking History Download.

We noticed that everytime we run netchange collections, the date parameters on the Purge HIstory Integration Interface are set based on
the dates chosen in the Concurrent program Shipment and Booking History Download.
 
But, when we run the concurrent program in Complete refresh mode, the dates on the Purge History Integration Interface are set with dates
which fall outside the history and hence no history is purged.  Is this intended design that History will not be purged during Demantra Complete
Refresh collections, and only during Net Change collections?


To answer your question, history should be purged for both net-change and refresh collections.  If this is not working please contact Oracle
Support

Note, the complete refresh collection is not meant for regular weekly runs.  Ideally it should be run only once (boot strap) and that too
only if you want to collect all the data from Order Management (OM) into Demantra.

You should run only net-change collection for regular runs.  Also for bootstrap data load, if OM has more data than required for Demantra,
net-change collection with appropriate date range should be run.

About

This blog delivers the latest information regarding performance and install/upgrade. Comments welcome

Search

Archives
« July 2015
SunMonTueWedThuFriSat
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
       
Today