exp/imp of a schema with BLOB objects

[mahajanakhil198]

Within a day or two, we are plannning to take export of a schema at a customer site containing BLOb objects. I haven't ever encountered BLOBs.

1. So, just wanted if there are any potential issues with BLOB exports that are to be taken care of. I just don't want to try taking export again as DB data for this schema is 100GB.

2. The used space as shown by DBA_extents for this schema is around 100G. What is the expected size of export dump? Though I know it will depend on the deleted rows in extents below high water mark. But still I want to know if the export utility compresses assuming there is no deletion of rows.

Also, I will use compress=y to cause single extent to be allocated during import.

3. Not in regards to above export/import, I have an old export file and I only know the name of schema whose export was taken. Is it possible to find out the names of tablespaces in which the data of this schema had resided when the export was taken. I need to for some internal testing.

[LKBrwn_DBA]

1) No problemo with exp/imp BLOBS

2) Export utility compresses similar to creating a CSV file with added control information. This means it obviosly skips deleted rows.
Beware of compress=y and large tables, you may not want a single extent.

3) To find out the DDL (storage, tablespaces, etc...) do: "imp / show=y"

PS: If you have full database export, you get ddl to create tablespaces, users, ext...

[mahajanakhil198]

Thanks! It would definitely help me.

[mahajanakhil198]

LKBrwn_DBA's:
One question that is actually unanswered:

Isn't there a way to predict the size of export dump file before actually taking export?

[LKBrwn_DBA]

For export utility, you may need to take a guess based on the amount of data in the tables you want to export.
For datapump you can generate an estimate before doing actual export.

[mahajanakhil198]

Yes, it helped. Export generated by expdp is more in size in comparison to exp utility. So, now I have an upper limit on the size of dump file.

To my wonder, I checked out all the parameters with impdp but didn't find any that would do the work like show=y in imp utility.

[vnktummala]

Hi Maha,

You can dump the DDL using SQLFILE parameter of impdp utility.

Thanks,

[mahajanakhil198]

Hmm thanks. Its been long now that I have used datapump.