moving CLOBs in ORACLE

check this out

http://gerardnico.com/wiki/database/oracle/external_table_clob


http://www.dba-oracle.com/t_insert_clob_table_column.htm

http://www.codeproject.com/Articles/14717/Inserting-up-to-32000-characters-in-an-Oracle-CLOB

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.

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);

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.

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

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

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.