Re: Copy Big Table from one Db to another

From: Sanjay Mishra <"Sanjay>
Date: Tue, 3 Mar 2020 18:31:13 +0000 (UTC)
Message-ID: <>

 One Good option that came out from Jonathan Update is to create new tablespace and using CTAS with parallel DML to add 6 new table in this tablespace and then move with TTS. Any expert view as this might be better way TxSanjay

    On Tuesday, March 3, 2020, 01:14:41 PM EST, Sanjay Mishra <> wrote:  

  JonathanYes it was also one of the good options but as the tablespace is big file with multiple other objects and so is the reason, I didn't list in my options. Definitely, if someone has used it and even it needs some downtime to copy the file can be a better option than other options, then can give a try. Our tablespace is currently 25 Tb that contains most of the required 6 tables that need to be copied of which only 2 are more than 2 TB and rest are few Gigs.  TxSanjay

    On Tuesday, March 3, 2020, 12:43:49 PM EST, Jonathan Lewis <> wrote:    

Any scope for using transportable tablespaces ? Even if you have to transport more than you want then drop the excess objects after the tablespace(s) arrive.

Jonathan Lewis

From: <> on behalf of Sanjay Mishra <> Sent: 03 March 2020 17:32
To: Oracle-L Freelists
Subject: Copy Big Table from one Db to another

Did anyone has done any benchmark and has created any details on the best method to copy a big table from one DB to another. I am looking for a 2-5 Tb tables to be moved within the same ASM storage or can say that these different databases share the same ASM disk group. Some options like

1. expdp/impdp - with optimization like parallel or so
2. Using DB Link and Insert Append
3. Using Copy command
4. Using sqlcl or other Oracle tool

Tables have multiple LOBs object and it is not partitioned. Any initialization parameter setting like increasing PGA for Direct operation or tweak that can save the time


-- Received on Tue Mar 03 2020 - 19:31:13 CET

Original text of this message