1

Database: Oracle 12c

I want to take single partition, or a set of partitions, disconnect it from a Table, or set of tables on DB1 and move it to another table on another database. I would like to avoid doing DML to do this for performance reasons (It needs to be fast).

Each Partition will contain between three and four hundred million records. Each Partition will be broken up into approximately 300 Sub-Partitions.

The task will need to be automated.

Some thoughts I had:

  • Somehow put each partition in it's own datafile upon creation, then detaching from the source and attaching it to the destination?
  • Extract the whole partition (not record-by-record)
  • Any other non-DML Solutions are also welcom

Example (Move Part#33 from both to DB#2, preferably with a single, operation):

 __________________              __________________
| DB#1             |            | DB#2             |
|------------------|            |------------------|
|Table1            |            |Table1            |
|   Part#1         |            |   Part#1         |
|   ...            |            |   ...            |
|   Part#33        |    ---->   |   Part#32        |
|      Subpart#1   |            |                  |
|      ...         |            |                  |
|      Subpart#300 |            |                  |
|------------------|            |------------------|
|Table2            |            |Table2            |
|   Part#1         |            |   Part#1         |
|   ...            |            |   ...            |
|   Part#33        |    ---->   |   Part#32        |
|      Subpart#1   |            |                  |
|      ...         |            |                  |
|      Subpart#300 |            |                  |
|__________________|            |__________________|
  • Take a look here see if it helps. Also 300-400m rows isn't all that bad if you have good hw but YMMV since do you not specify time constraint except "fast" which can mean anything. – Raj Jul 22 '16 at 18:32
  • Thanks, Jaj. That looks like what I'm looking for. As to you're other comment, the Data would need to transfer over the network and several of the tables that need to move have two to three hundred columns. 300m-400m records over a DB Link under those conditions take a rather large amount of time. – ScrappyDev Jul 25 '16 at 12:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.