Export/Import BLOBS ... is there a better way to handle? Options

[angus]

Hi folks,

The company I'm contracted to is making use of Amazon Web Services, in particular, we are using Amazon RDS with version Oracle 11gR2 Standard Edition. Unfortunately, Amazon does not support data pump but export/import is available.

We don't have a lot of tables (<50) in our database at present but a couple have over 4 millions rows. Also, some contain blobs.

In my export, I've made use of such parameters as ...

DIRECT=Y
RECORDLENGTH=65535

which has optimized the export of the "non-blob" tables somewhat. The tables housing blobs, of course, resort to the conventional path. As I understand it, BLOBS are processed one row at a time. So these tables are taking quite a bit of time to export.

my question: is there a way to handle the export/import of blobs which would be much more efficient?

Are there any other recommendations, overall, that could make an export/import work better?

Thanks in advance.

[burleson]

Hi Angus,

>> making use of Amazon Web Services

You have my condolances.

I would complain STRONGLY for being forced to use a deprecated utility.

****************************************
>> is there a way to handle the export/import of blobs which would be much more efficient?

Check out my notes in improving import performance:

http://www.dba-oracle.com/oracle_tips_load_speed.htm

You can export BLOBS with Java and utl_file but AWS may not allow it:

http://www.dba-oracle.com/t_export_unload_blob_clob.htm

http://www.dba-oracle.com/t_java_graphics_blob_retrieval.htm

http://www.oracle-base.com/articles/9i/export-blob-9i.php

Check out this book, it has advanced techniques for import/export:

http://www.rampant-books.com/book_0801_oracle_utilities.htm

[angus]

Thanks Donald, for your notes and condolences. :-)

[brandedfundoo]

Thanks Donald, for your notes and condolences. :-)

and for forced publicity too !!!

[Bambi Wang]

If you can use JAVA and JDBC, you can use getBytes and setBytes to copy BLOBs from one database to another database. Here is a sample.

Blob blob;
byte[] streamData;

blob = rs.getBlob(i + 1);
streamData = blob.getBytes(1, (int) blob.length());
insertStatement.setBytes(i + 1, streamData);

It is at least 5 times faster than data pump or export/import.

My team have developed a tool using above code and multi-threading, batch processing, etc. It is over 30 times faster than data pump for copying BLOB or CLOB. If anyone is interested, feel free to contact me for details.

[kdinesh]

If you can use JAVA and JDBC, you can use getBytes and setBytes to copy BLOBs from one database to another database. Here is a sample.

Blob blob;
byte[] streamData;

blob = rs.getBlob(i + 1);
streamData = blob.getBytes(1, (int) blob.length());
insertStatement.setBytes(i + 1, streamData);

It is at least 5 times faster than data pump or export/import.

My team have developed a tool using above code and multi-threading, batch processing, etc. It is over 30 times faster than data pump for copying BLOB or CLOB. If anyone is interested, feel free to contact me for details.

Interested in knowing how you made it 30 times faster as i am also trying the same thing. my db server is on the network and the bandwidth is blocking the throughput.