Resolving problem with BLOB, clob Field table in remote database using Dblink mode

In the database synchronization we often use the Dblink method. Dblink can logically treat multiple databases that are physically stored on the network as a single large database. Developers need not care about the network distribution of the database, it is easy to implement from the different databases to read data. How to set up Dblink between Oracle databases I've been involved in the previous article, and I'm not going to take the space to explain it. For details, see Oracle Build Dblink.

This article mainly explains how to solve the problem that the Dblink way can't realize the data synchronization of BLOB and Clob field. There are a lot of cattle people on the Internet have given the solution, in reference to their solutions to solve the problem, found that some small problems have not been solved, and no one to give a complete solution process. Here I record my settlement process for your reference.

First, let's take a look at why Dblink cannot implement the synchronization of Blob, CLOB fields. I set up the database Link in my native Oracle database for DEVD databases. The data can be queried when we select a table with a DEVD database that does not have large fields. When we look for a table with large fields, the system gives the following hints:

SELECT * from [email protected]

There are also BLOB fields in the Jjr_tjjr_cyzgxx table.

Because Oracle does not allow the LOB attribute's data to be queried in a dblink manner. To solve such problems, some people propose to use temporary tables to relay, and then synchronize the data. Using this approach does solve the problem, and the specific solution is as follows:

1. Use subqueries to create temporary tables that enable local temporary tables to replicate data from remote database tables.

Create Global temporary Table temp on commit preserve rows as select * from [email protected]

Why do I use on commit preserve rows, which I have already mentioned in the summary of the previous Oracle query for creating temporary tables with subqueries.

By querying the temp table, we found that all the data in the Jjr_tjjr_cyzgxx table was synchronized to the temp table.

2. Synchronize temporary table temp data in the local test table, delete the data from the test table, and then use the subquery for data replication.

Insert into Test select * from Temp

By querying the local test table, we see that even the data for the table containing the BLOB field has been queried.

Other solutions:

Is it possible to synchronize data with large numbers of tables through temporary tables, and can we use local tables to achieve the same effect? Through experiments we find that it is possible to do this directly through local tables.

First, the Test1 table is established and the structure is consistent with the JJR_TJJR_CYZGXX table structure. Data replication via subqueries

Insert into test1 select * from [email protected]

By querying the Test1 table we find that the data has been copied.

--To create a temporary table:CreateGlobalTemporary TableLob2 ( line Number Primary Key,   textClob) on Commit Deleterows;--Insert Data:Sql> Insert  intoLob2SelectLinetext  fromLob1@lob_link;499rows created. SQL> Select *  fromLob2whereRowNum<5; LineTEXT---------- ---------------------------------------------------------------------         5         8Type Number  isnumber_base; 9SubtypeFLOAT  is  Number;--Number (126)         OneSubtype "DOUBLE PRECISION" is FLOAT; SQL> Commit;CommitComplete . SQL> Select *  fromLob2whereRowNum<5; no rows selected--The data is deleted after the commit, and this is the attribute of the temporary table. 

Resolving problem with BLOB, clob Field table in remote database using Dblink mode