asked on

Need to do bulk export/import of BLOB data in Oracle 10g databases

I work for a cruise line and our databases are developed in our corporate IT office before deploying them out to our ships. We have personnel that have their photo embedded within the Oracle 10g database as BLOB data. There are about 258 records that we need to export the BLOB data for and then import that data into the 10g database on each ship. We already do bulk exports/imports of the text data but now our new directive is to push the corresponding photos with them.

Using TOAD we can extract one BLOB object at a time but we need to find an application (or script) that will allow us to do this in bulk - quickly and efficiently - and then do the bulk import of the same data to each of the other Oracle 10g databases we have.

Would anyone have any suggestions?

Thank you.

Rich Olu

Have you tried imp/exp or dpimp/dpexp (datapump equivalent)? These would export all objects owned by a particular user including BLOBS and you can import them into the target location using imp or dpimp.

You may also you transportable tablespaces. Are teh source and destination same Oracle versions?

R.

schwertner

If this is Oracle 10g R2 you can create only the tablespace where the LOBs table reside.
After that using Data Pump export the table and then Import it.
The Data Pump utilities are:
expdp
impdp

bergmanb

ASKER

What we are trying to do is to export certain EMP records and pictures from one DB and import them into a different DB. We will not be able to use the above EXPDP and IMPDB functionality as we have data in the tables that we need to keep and in addition add the data that was exported.

Thanks for your valuable feedbacks!

ASKER CERTIFIED SOLUTION

Rich Olu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bergmanb

ASKER

Thanks guys! I will look at this solution and let you know if it works out.

Much apprecaited!

ranganadhkodali

Hi,

SQL server Export/Import wizard will make you do very simple with rich graphical interface. Most notable thing is it will support data transfer to and from all major sources ... like excel.Sql server, acces.... Oracle and so ...many OLEDB Source ....

Thanks And Regards
Ranganadh Kodali

upss

you may want to check fastreader. www.wisdomforce.com As they claim on the web pahe, fastreader used when "Have a need to quickly check the data from columns with BLOB, CLOB, XMLTYPE data type or Spatial objects." And also: "Performing fast selective backup of huge tables.
Very quickly export Oracle tables into a portable text format for selected columns, and/or filter rows by setting conditions."