This discussion is archived
6 Replies Latest reply: Mar 21, 2013 5:55 AM by Gowin_dba RSS

How do I migrate Blobs?

Gowin_dba Newbie
Currently Being Moderated
Hi,

I have a scenario of table refresh with having BLOB column from prod to dev.
Say ex, MASTER_ACCOUNT table in prod having BLOB column (name ATTACH_ID), every night or weekly application team want to refresh in dev region, where having same set of table. i.e.., MASTER_ACCOUNT table in dev having column BLOB (name ATTACH_ID) too.

Source / Target version : Oracle 11.2.0.3

How can we succeed?

Thx,
Gowin.
  • 1. Re: How do I migrate Blobs?
    Mark D Powell Guru
    Currently Being Moderated
    What are the restrictions on how the test table is updated? Do you need to preserve the existing table structure as is? Do you need to preserve any of the existing data? Do you only want to update the rows that exist in test or do you want to bring all the data in production to test for these tables?

    If you can replace the entire table you could use export/import since with an export of the base table the blob is associated with would also export/import the blob segment.


    HTH -- Mark D Powell --
    corr typo waht to what

    Edited by: Mark D Powell on Mar 20, 2013 6:16 AM
  • 2. Re: How do I migrate Blobs?
    moreajays Pro
    Currently Being Moderated
    Hi Gowin,

    You can use exp/imp or expdp/impdp

    Thanks,
    Ajay More
    http://www.moreajays.com
  • 3. Re: How do I migrate Blobs?
    Gowin_dba Newbie
    Currently Being Moderated
    Mark,

    We can truncate data in dev before inserting prod data. Structure in dev is same as that of prod. Not having any specific restriction.
    If possible they are looking for option like SQL loader & insert in to dev table via db links.. i.e.., without DBA intervention.
    where as expdp/impdp requires DBA.
  • 4. Re: How do I migrate Blobs?
    Mark D Powell Guru
    Currently Being Moderated
    The sqlldr utility can definitely be used to load blob data. Someone would just need to develop a program to extract the data into a file or series of files (one per blob), copy the files to the test server, and then run sqlldr to insert the data.

    You could also look to see if Oracle has removed the restriction that did not support the insertion of BLOB data in a distributed transaction. If this restriction has been removed then a truncate followed by a simple insert/select in turn followed by a commit might be all you need.

    HTH -- Mark D Powell --
  • 5. Re: How do I migrate Blobs?
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    You could probably use impdp directly without exporting data actually, by using NETWORK_LINK (a db link), and taking data only (avoiding meta-data). I remember it was not working with LONG datatype, but it should work with LOB.
    Find out more :
    http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL919

    Nicolas.
  • 6. Re: How do I migrate Blobs?
    Gowin_dba Newbie
    Currently Being Moderated
    Hi,

    I wrote procedue to copy table via dbms_datapump.metadata.. & granted necessary privs to users. Its working fine.

    Edited by: Gowin_dba on Mar 21, 2013 6:25 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points