Re: Copy Big Table from one Db to another

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 3 Mar 2020 18:36:28 +0000
Message-ID: <LNXP265MB15628BA8C3AEF009A0E698FDA5E40_at_LNXP265MB1562.GBRP265.PROD.OUTLOOK.COM>

Definitely an idea to consider with two possible benefits

1. The resulting tablespace might be a lot small when it comes to the physical move
2. The number of objects that have to be "meta-"exported and imported could be significantly smaller and there are some oddities with the metadata export at present that can waste a lot of time.

Additionally - there could be some benefits in creating or two tablespaces per table when you do the CTAS (less contention on tablespace header for space allocation - though that might be a small fraction of the workload), plus multiple copy processes when you move the files around, plus slightly easier to handle if you ever need to do something similar again.

(One or two -- one for the table segment, one for the LOB segment).

Regards
Jonathan Lewis

---

From: Sanjay Mishra <smishra_97_at_yahoo.com> Sent: 03 March 2020 18:31
To: Oracle-L Freelists; Jonathan Lewis; Sanjay Mishra Subject: Re: Copy Big Table from one Db to another

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

Tx
Sanjay

On Tuesday, March 3, 2020, 01:14:41 PM EST, Sanjay Mishra <dmarc-noreply_at_freelists.org> wrote:

Jonathan
Yes 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.

Tx
Sanjay

On Tuesday, March 3, 2020, 12:43:49 PM EST, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> 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.

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Sanjay Mishra <dmarc-noreply_at_freelists.org<mailto:dmarc-noreply_at_freelists.org>> 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

TIA
Sanjay

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Tue Mar 03 2020 - 19:36:28 CET