Dblink to remote database

Oracle Database Synchronization

The test is successful between two oracle databases. The following is a summary:

Requirements:

There are two oracle database servers A and B (A and B can be on the same Intranet or two independent machines on the Internet ). Both A and B have testable tables with the same structure. Now, when the testable table in database A changes, the testable data in database B also changes accordingly.

My solutions:

Create A link to database B in database A, create A synonym for the table to be synchronized, and create A trigger. Of course, the current user you are using must have the appropriate permissions to perform these operations.

When synchronizing data from A to B, all settings should be made on:

1. To ensure connection to the database of another remote server, you need to create a DB Link. However, pay attention to the syntax format: using + "connect string ", this connect string should exist in the TNSNAMES of the oracle server. in the ORA file, the listener obtains the remote server from here.

The IP address and other information. I have defined a '123' connect string as follows:

251 =
(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.20.) (PORT = 1521 ))

)

(CONNECT_DATA =

(SERVICE_NAME = mychoice)

)

)

Store it in your TNSNAMES. ORA file.
2. Then you can define the DB Link:

Create public database link TEST2.US. ORACLE. COM
Connect to user name

Identified by "password"

Using '000000 ';

3. Create synonym (synonymous)

Create or replace synonym TEST01

For MYCHOICE.TESTABLE@TEST2.US.ORACLE.COM;

After the creation, you can use:

Select * from test01

The preceding statement is equivalent to executing on server B:

Select * from testable

4. feudal triggers:

When the testable table in Table A changes (insert operation is only considered here), the corresponding data will be inserted to the testable of remote database B:

Create or replace trigger rtest
After insert on testable

For each row

Begin

Insert into test01 (something) values (: new. something );

End;

OK. Now we can test it. you can insert A record to the testable table in database A to see if database B has been added accordingly.

A problem occurs when a table with blob fields is inserted during synchronization. The oracle trigger cannot directly access the blob field of the remote table,
An error is reported: the lob locator selected from the remote table cannot be used.
Solution: 1. Create a temporary table with the same structure as the table containing blob fields in the local database.
2. Insert the newly inserted data into the temporary table.
3. insert the data in the temporary table into the remote database in the form of insert into remoteTable @ dblink_name select * from temp.

The sample code is as follows:
Create global temporary table temp as select * from test (contains blob field, field name is file) where 1 = 2;
Trigger:
Create or replace trigger test_trigger
After insert on test
For each row
Begin
If inserting then
Insert into temp (id, file, name) values (: new. id,: new. file,: new. name );
Insert into test @ dblink_name select * from temp (only select * can be used for insertion, otherwise an error will be reported)
Where id =: new. id;
End if;
End;