moving CLOBs in ORACLE

Evan Cutler
Evan Cutler used Ask the Experts™
on
Greetings,
I have a table with a CLOB in it (plus some normal fields, couple of decimals, some ints, some varchar2(x)).

I am told that "Insert into table (select....))" is not the fastest way to load a CLOB.

Does anyone know the fastest way to move a CLOB over (with the other fields)?
Thanks

Efficiencey is the name of the game here.  Source and Target tables are both in ORACLE instances.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Evan CutlerVolunteer Chief Information Officer

Author

Commented:
This is great information...I will absolutely keep this...we have attachments too, to which I was going to tackle next, this is great.

However, my problem is I need to pick up some tables that are already CLOB'd, and leapfrog to one table then onto another.  

basically I need to copy this table twice.  My ETL job is extremely slow in this matter.
Thanks.

Commented:
Oracle recommends to keep LOB, CLOBS in separate tablespaces and you could

1. Create a new tablespace and
2. move all your CLOBS to new ts

Here is a generate script

select 'alter table '||table_name||' move lob('||column_name||') store as (tablespace MY_NEW_TS);' from user_lobs
/

this script will generate all the tables with lob/clob columns using which you can move them to a separate tablespace

for example

alter table clob_test move lob(clob_field) store as ( tablespace users);
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Evan CutlerVolunteer Chief Information Officer

Author

Commented:
The problem is that I have three schemas.....one source, two target.  Each schema is in a different tablespace, and I need to get this clob over to both of them, because I have applications using each schema.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>I am told that "Insert into table (select....))" is not the fastest way to load a CLOB.

Have you confirmed this with your own tests?  Not sure how you are doing it now but I would expect this to be the quickest.
Evan CutlerVolunteer Chief Information Officer

Author

Commented:
I have a test of 300,000 records...create table from insert...  runs in 4.5 minutes.
I have 220Mil records to move. then 100K a day....
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
If you are only inserting and rarely deleteing you can speed the insert up with the APPEND hint to insert the new rows above the high water mark.

Have you experimented with datapump (expdp/impdp)?
Evan CutlerVolunteer Chief Information Officer

Author

Commented:
sorry for the long reply.
impdp is a great solution, however my customer would not allow it.
we had to alter the plan.

Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial