6 Replies Latest reply: Mar 21, 2013 7:55 AM by Gowin_dba RSS

    How do I migrate Blobs?


      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

      How can we succeed?

        • 1. Re: How do I migrate Blobs?
          Mark D Powell
          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?
            Hi Gowin,

            You can use exp/imp or expdp/impdp

            Ajay More
            • 3. Re: How do I migrate Blobs?

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

                  • 6. Re: How do I migrate Blobs?

                    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