Currently we are developing a huge datawarehouse system. Some tables contain billions of rows.
The requiremnts is to move records that is more that 18 mths to a historical database where it would be kept for th next 1.5 years. Thereafter it should be kept offline for 10 years.
What we had done so far.
1. Create two database - active and historical.
2. Create partition tables (range partition by month)for those huge tables. - So that we can easily drop a partition monthly after six month.
1. We had yet to decide on how to move the partition form the active database to the historical database. We are considering import/export utilities , copying through dblinks and transportable tablespace.
Any recommendations.
2. For archiving of historical data (records that are more than 2 years and to be kept offline for 10 years) what would you think should be the most appropriate format for these data to be stored offline. FYI , we are given 3 days lead time to make the archive data online. We are considering export files while by developers recommends xml format. Do you know what is the industry practices ot deal with huge databases. Any recommendations?
Locked
