Database DataWarehousing (MOSC)

MOSC Banner

The best way to "Move" a partition between two databases

edited Jul 19, 2010 5:02AM in Database DataWarehousing (MOSC) 4 commentsAnswered
In our data warehouse environment  we have an archiving process that "moves" partitions between two databases - out of our production server and into an historical (archive) database.
The way we do it now is by:
1. Creating a view to query the original table at a specific partition.
2. CTAS the table on the other instance using dblink (no parallel).
3. When the table is created, we exchange-partition it into the table on the historical database.
4. Few days later, we will drop the old partition from our production server.


We do it for ~450 partitions at ~70 tables (around 1TB is being transfered each month) and we'd like to speed up the process

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center