We have a primary table that is Range partitioned by date with a 1-month interval. It's also a list sub-partitioned with 4 distinct values. So essentially it is one month partition having 4 sub-partitions.

Database: Oracle 19c

I need advice on how to effectively move the partition/sub-partition data from active schema to historical schema in another database.

Also, there are about 30 tables that are referenced partitioned on the primary table for which the data needs to be moved as well. Overall I'm looking to move about 2500 subpartitions

I'm not sure if an exchange partition would be the right approach in this scenario?

TIA

CodePudding user response：

You could use exchange to get the data rapidly out of your active table, but you would still then to send that table over the wire to the remote history database to load it in.

In which case, using "exchange" probably is just adding more steps to the process for little gain. (There are still potential uses here depending on how you want to handle indexing etc).

But simplest is perhaps just transferring the data over, assuming a common structure between the two tables, ie

insert /*  APPEND */ into history_table@remote_db
select * from active_table partition ( myparname )

I can't remember if partition naming syntax is supported over a db link, but if not, then the appropriate date predicates will do the same trick, and then just follow up with:

alter table active_table truncate partition myparname;