Home » SQL & PL/SQL » SQL & PL/SQL » How to Move fast the data from one database to another database ? ( )
How to Move fast the data from one database to another database ? [message #659133] Fri, 06 January 2017 10:34 Go to next message
sivaparam
Messages: 16
Registered: March 2005
Junior Member
The table in the database is monthly or daily partitioned, it is holding about 10 month worth of data.
Each month there is about 50 to 70 MILLION records.
I want to run the job daily or monthly and move data which is 6 month old from source database to target database.
Source and target database is having same table structure.
Any pointer is much appreciated.

In the example below, I like to move Part#33 from source database to target database.


PS:


 __________________              __________________
| DB#1             |            | DB#2             |
|------------------|            |------------------|
|Table1            |            |Table1            |
|   Part#1         |            |   Part#1         |
|   ...            |            |   ...            |
|   Part#32        |            |   Part#32        |
|   Part#33        |    ---->   |                  |
|      Subpart#1   |            |                  |
|      ...         |            |                  |
|      Subpart#300 |            |                  |
|------------------|            |------------------|
|Table2            |            |Table2            |
|   Part#1         |            |   Part#1         |
|   ...            |            |   ...            |
|   Part#32        |            |   Part#32        |   
|   Part#33        |    ---->   |                  |
|      Subpart#1   |            |                  |
|      ...         |            |                  |
|      Subpart#300 |            |                  |
|__________________|            |__________________|



[Updated on: Fri, 06 January 2017 10:58]

Report message to a moderator

Re: How to Move fast the data from one database to another database ? [message #659134 is a reply to message #659133] Fri, 06 January 2017 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Export the partition and import it in the target.
Or even, as 70 millions is not big, directly import it through a database link.
Note: I talk about Data Pump.
And I assumed that "move" means "copy", if it is really a move then use partition exchange.

Re: How to Move fast the data from one database to another database ? [message #659135 is a reply to message #659133] Fri, 06 January 2017 13:22 Go to previous message
Bill B
Messages: 1718
Registered: December 2004
Senior Member
You can't exchange partitions over a database link but the following will work fine

1) Copy the existing partition into the other table

insert into my_table@other_db
select *
from my_table partition(part_name);

2) drop the partition and rebuild any global indexes.

ALTER TABLE my_table DROP PARTITION part_name UPDATE GLOBAL INDEXES;

The above command will require exclusive access to the table. in you are on oracle 11 or above you can do the following. While the index is being rebuilt it will not be available but the (select/update/delete) will still work fine but performance will be effected until the index is active again.

ALTER TABLE my_table DROP PARTITION part_name;
alter index my_table_global_idx rebuild online; (repeat for all global indexes on my_table);

[Updated on: Fri, 06 January 2017 13:25]

Report message to a moderator

Previous Topic: Shrink space
Next Topic: execute immediate
Goto Forum:
  


Current Time: Wed Feb 21 09:21:06 CST 2018

Total time taken to generate the page: 0.01255 seconds