Copy data from one specific partition of a table to another database

Toad for Oracle - Beta
1

Toad Version: Toad for Oracle Xpert Edition (64-bit) 24.2.275.4664

Question: How can we copy a specific partition data from a table to another database of same table. TOAD table import options not supporting partition option.

2

The fastest way would be to use a database link, and run a query like this in the editor with F9

insert into table@target_db select * from table partition (partition name)

If you can't create a database link, you can do this in Toad's "Copy Table Data" window.
Right-click the table in the schema browser and choose "Data -> Copy to another schema"
In the window that appears, click the "tables" tab, then add a "where" clause to copy the rows that you want to include. It doesn't allow you to specify a partition, but you can specify a where clause that will get the same rows.

3

Thanks for your response, DB Link option is not allowed. I am looking specific partition data to be copied from one database to another for same table as volume is around 50 million records. Toad partition copy is not available ?

4

with 50 million records, you might want to try again to see if you can get permission to create a DB Link. If you can't create it from source to target, maybe you can create it from target to source.

Toad partition copy is not available ?

No, we don't have this, and even if we did, it would be too slow for that many records. You might want to look at data pump if you can't copy using sql with a DB Link.

1 Like