What is the best way to move big tables with CLOBC between databases ?

We have around 20 big tables in our oracle database which have a CLOB field. we want to upgrade to Oracle 11g, using a new installation of the 11g database and import the data from old database. the problem is that export/import takes so much time for the CLOB tables, that we dont have the time window for it. ( all other tables, even very large don't have this performance issue)

What is the best way of moving large tables with CLOB field between databases?

Who is Participating?
sdstuberConnect With a Mentor Commented:
sqlloader could be fast to load, but you still have to get the data unloaded.

You can move clobs across db links,  but there are a lot of limitations to doing so and not just in 9i.
as simple insert-select should work though.

insert my_11g_table(myclob) (select my_clob from remote_9i_table@dblink)  

I use the above to move a clob between 9i and 10g db's
you can try pushing the data across database link or parallelize your export/import.  Do one for each table instead of bundling them.
structuredwebAuthor Commented:
Thanks for the quick response.

I just tried pushing CLOB's ( or any LOB ) over dblink, and is unsupported -
see note 119897.1 on metalink.
I tried a few workarounds ( selecting only the CLOB from the remote, creating view over the remote table etc ... ) which none worked.

as for parallelizing the import, that might help, but still, a table with millions of records, with CLOB's will take a lot of time to import.

any other ideas? ( sqlloader, moving CLOB's to files and reload, procedure that convert them to char chunks and build them in the other end .... )

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

structuredwebAuthor Commented:
You are right it does work with the syntax you provided.
I tried to speed test it, and got even worse time then bringing the whole table using import -
updating 30K CLOB field records, out of 3M records table, is taking more then an hour, which is the time that it took to bring the entire 3M table using import.

how many records did you moved? did you have any configuration to SQLNet? ( which i can see wait evens for ) and/or any other configuration to make it faster ?
I never moved anywhere close to 3M records.  Most ever was maybe a few thousand.  The clobs I moved ranged from a few dozen bytes to a few meg, but total volume was still probably less than you are trying to move.

I did not make any special sqlnet config.
SujithData ArchitectCommented:
You could explore transportable tablespaces.

an introduction here.
I'll try to help more if the author has further ideas or issues. but I don't see it getting a lot faster.

30K x 3M rows = 90G of data.  In one hour, that's really not too bad of a time.  I understand you might not want that long of a lag, but that's a lot of data to move in a short period of time.
You can help the import by preallocating the target tablespace

Another option is to import everything except the big data, then import just the rows that will actually be needed for the first few hours of user activity.  While users go about their business, you can back fill the remainder of the data.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.