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

We have around 20 big tables in our oracle 9.2.0.8 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?

Thanks
LVL 1
structuredwebAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
you can try pushing the data across database link or parallelize your export/import.  Do one for each table instead of bundling them.
0
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 .... )

Thanks
0
sdstuberCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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 ?
0
sdstuberCommented:
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.
0
SujithData ArchitectCommented:
You could explore transportable tablespaces.

an introduction here.
http://www.oracle-base.com/articles/9i/TransportableTablespaces9i.php
0
sdstuberCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.