[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 614
  • Last Modified:

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
0
structuredweb
Asked:
structuredweb
  • 4
  • 2
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
sujith80Commented:
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now