Q

Exporting with LOBs

An Oracle user asks how to make sure LOBs get exported when doing a schema level export.

I am doing a schema level export (9.2.0.3) for archiving a schema, and there are LOBs (CLOBS and BLOBS) in some of the tables. The export completes without any problems, but the DMP file size is 80 MB, while some of the LOB files are easily over 500 MB, clearly indicating that they are not being exported. Can you tell me if there's a way to make sure the LOBs get exported?
The best way to make sure the data is getting exported is to import from the dump file! Create a test user (I'll call it BOB) and then import from the original user (I'll call it SCOTT) into the test user.

imp userid=system/manager file=my_dmp.dmp log=imp.log fromuser=scott touser=bob

After the import completes, verify if the LOBs are present in the BOB schema. If they are, then you know the dump file contains the LOBs.

This was first published in February 2008

Dig deeper on Oracle database administration

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close