Toad World® Forums

COPY table


#1

Hi, I need make a copy of a table with more than 5 million of records, this table has a BLOB field and lob’s size is 430 Gb, wich toad option may I use?

Regard R.


#2

Data Subset Wizard – just choose a subset of 100%

From: raranibar [mailto:bounce-raranibar@toadworld.com]

Sent: Tuesday, July 30, 2013 10:38 PM

To: oracle@toadworld.com

Subject: [Toad World - Oracle Discussion Forum] COPY table

COPY table

Thread created by raranibar

Hi, I need make a copy of a table with more than 5 million of records, this table has a BLOB field and lob’s size is 430 Gb, wich toad option may I use?

Regard R.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Oracle
notifications altogether.

Toad World - Oracle Discussion Forum

Flag
this post as spam/abuse.


#3

Thanks Bert, something I forget to mention is that the copy of the table is in the same schema and server; we need make a depuration process of data to chance the lob segment location to new tablespace. In the data subset wizard requires that source and destination be different


#4

DBMS_REDEFINITION wizard should do what you’re looking for – might be part of DB Admin module ….

From: raranibar [mailto:bounce-raranibar@toadworld.com]

Sent: Wednesday, July 31, 2013 8:26 AM

To: oracle@toadworld.com

Subject: RE: [Toad World - Oracle Discussion Forum] COPY table

RE: COPY table

Reply by raranibar

Thanks Bert, something I forget to mention is that the copy of the table is in the same schema and server; we need make a depuration process of data to chance the lob segment location to new
tablespace. In the data subset wizard requires that source and destination be different

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Oracle
notifications altogether.

Toad World - Oracle Discussion Forum

Flag
this post as spam/abuse.


#5

You could do this very easily with a PL/SQL block, depending on what your goal is.

If you don’t care about updates to the source table’s rows that have already been copied, it’s about 15-20 lines of code. I could write you something in 10 minutes.

If you’re planning on transitioning to the new table and the source is active during the copy, materialized view logs would be necessary to track rows changed.

Oracle has a package for online redefinition, but it is meant to copy and then swap out the source table. It doesn’t seem to do very well for large tables.


#6

HiJp, no matter with updates and yes I will transitioning to the new table, but no problem because when we execute the proceses no transacction will be execute


#7

HiJp, no matter with updates and yes I will transitioning to the new table, but no problem because when we execute the proceses no transactions will be execute


#8

I can write something up.

Couple questions though.

You mentioned a different server. Do you have a database link to this server?

Are there any other changes such as column ordering, additions, removals, or modifications?


#9

I’m proving the following procedure:

I create a target table, then I executed an insert using a select, this will take 16 hours


#10

No is in the same server Jp, and just need dump the information from source table to target table


#11

Yep, 16 hours sounds fair for such a huge amount of data. Are any of the clobs duplicates? Deduplicate provides amazing performance if you have dupes. Make sure you have frequent commits… and do an insert /*+ append */


#12

unfortunately there are no duplicates,