DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: exp/imp of a schema with BLOB objects

  1. #1
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322

    exp/imp of a schema with BLOB objects

    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.
    lucky

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool No problemo

    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...
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Thanks! It would definitely help me.
    lucky

  4. #4
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    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?
    lucky

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Question Take a guess.

    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.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  6. #6
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    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.
    lucky

  7. #7
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Hi Maha,

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

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  8. #8
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Hmm thanks. Its been long now that I have used datapump.
    lucky

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width