How we can select blob data from One server another server using dB link

Question

I have two database, One is for data loading and another one for data Moniter, I need to show a blob content data from data loading server using dB link, i tried but i can't able to do, any option to download the blob content from one server to another server with dB link.

Normal select statement in Oracle

Answer 1

You can move LOBs across a database link as long as you are moving from table to table, not merely selecting. This avoids the use of temporary LOB locators, which is what the restriction is on.

So, you can select LOB (CLOB or BLOB) content over a database link in a couple of ways:

1. Use a CTAS (CREATE TABLE AS SELECT ...) to pull the data and write it locally. Internally with data movement Oracle is able to work with remote LOB locators despite the typical error message to the contrary.

2. INSERT the data to a local table with INSERT SELECT.

3. Use a MERGE or UPDATE on an existing local table that already has all the non-LOB columns to update the LOB columns with the remote LOBs.

4. If your CLOB is < 4KB or BLOB < 2KB, (using remote dbms_lob calls to test and substring it dbms_lob.getlength@dblink(...) , dbms_lob.substr@dblink(...)) you can then cast it as varchar2(4000) or raw(2000) and that will pull very fast in any normal SQL, much, much faster than the same data in a LOB data type. For example:

   SELECT CAST(dbms_lob.substr@dblink(myblobcol,2000,1) AS raw(2000))
     FROM remotetable@dblink
    WHERE dbms_lob.getlength@dblink(myblobcol) <= 2000
   

   Obviously this can't help with oversize values, but one technique for speeding up large quantity LOB transfers over links is to first pull the data without the actual LOBs but with ROWIDs into a temp table, then pull the small LOB values this way, update the temp table and clear the ROWIDs for those rows, then merge on all the remaining ROWIDs pulling the full LOBs, which will be only the oversize ones. The complexity may not be worth using this technique commonly, but it could be useful in situations where the volume is particularly high and most of the values are less than varchar2/raw limits.

(Note: I wish this were better documented, but we're left guessing a bit as to why SELECT fails but DML works. I suppose based on the error message that in SELECT statements Oracle may be trying to implicitly create local temporary LOBs and the locator for this has to be sent to the remote to do the copy, which isn't allowed, probably due to addressability restrictions of temp segments outside the owning private process. This is of course just guesswork. Whatever's really happening internally, the problem is only for SELECT statements. DMLs work with pubically-accessible permanent LOB locators so don't seem to have an issue)