Oracle multi-database data is updated at the same time

Oracle multi-database data is updated at the same time

1. Create dblink

Create database link test connect to mall identified by test using'
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 113.116.216.60) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
';

2. Create a synonym

Create or replace synonym store for sale_record @ mall;

Run the following SQL statement to view the table content of the remote database:

Select * from store;

3. Create a table and Index

Create table sale_record (
Id int primary key,
StoreId int not null,
Saledate date not null,
ProductId int,
Num varchar (30) not null
);

Create sequence sale_record_seq
Increment by 1 -- add several
Start with 1000000000 -- count from 1
NOMAXvalue -- do not set the maximum value
NOCYCLE -- always accumulate without repeating
CACHE 10;

4. Create a trigger

Create or replace trigger addSaleRecords
Before insert on sale_record
For each row
Declare
-- Local variables here
V_ID varchar (10 );
V_storeId number (10 );
V_saledate date;
V_productId number (10 );
V_num varchar (40 );
Begin
Select sale_record_seq.nextval into: new. id from dual;
V_ID: =: new. id;
V_storeId: =: new. storeId;
V_saledate: =: new. saledate;
V_productId: =: new. productId;
V_num: =: new. num;
Insert into sale_record @ mall (id, storeId, saledate, productId, num) values (v_ID, v_storeId, v_saledate, v_productId, v_num );
End addSaleRecords;

Test:

Add data to the local table:

Insert into sale_record (id, storeId, saledate, productId, num) values (sale_record_seq.nextval, 10000001, '2017-12-12 12:12:12 ', 2013, 11 );

Commit;

Run:

SQL> conn test/test@113.116.216.60: 1521/orcl
SQL> select * from sale_record

You can see that the same data is added to the remote database.

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian