The best way to "Move" a partition between two databases
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
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
0