gg oracle tjs 同步_同步Oracle数据库表到另一个Oracle数据库

同步A数据库表test_user到B数据库

1.创建A/B数据库用户

A数据库

IP:192.168.1.20

test/123456

B数据库

IP:192.168.1.21

test/123456

1.1.创建用户(SYSTEM用户)

创建用户

CREATE USER TEST IDENTIFIED BY 123456;

修改用户密码

ALTER USER TEST IDENTIFIED BY 123456

1.2.授予角色权限

-- ROLES

GRANT "CONNECT" TO "TEST" ;

1.3.授予系统权限

授予权限

-- SYSTEM PRIVILEGES

GRANT CREATE ANY PROCEDURE TO "TEST" ;

GRANT CREATE ANY SEQUENCE TO "TEST" ;

GRANT CREATE ANY TRIGGER TO "TEST" ;

GRANT CREATE ANY TABLE TO "TEST" ;

GRANT UNLIMITED TABLESPACE TO "TEST" ;

GRANT CREATE DATABASE LINK TO "TEST" ;

移除权限

REVOKE SELECT ANY PROCEDURE FROM "TEST";

2.创建建A/B数据库测试表test_user(TEST用户)

创建表

CREATE TABLE TEST_USER(

ID number(15) NOT NULL PRIMARY KEY,

USER_NAME VARCHAR2(255) default '' NOT NULL,

USER_PASS VARCHAR2(255) default '' NOT NULL,

CREATE_TIME DATE NOT NULL

);

comment on column TEST_USER.ID is '主键';

comment on column TEST_USER.USER_NAME is '用户名';

comment on column TEST_USER.USER_PASS is '密码';

comment on column TEST_USER.CREATE_TIME is '创建时间';

3.创建DBLINK(A数据库)

创建DBLINK

create database link DBLINK_TEST connect to TEST identified by "123456" using '192.168.1.21:1521/XE';

删除DBLINK

drop database link DBLINK_TEST;

测试DBLINK是否成功

select * from TEST_USER@DBLINK_TEST;

4.创建触发器(A数据库)

create or replace TRIGGER TRIGGER_SYN_TEST

AFTER INSERT OR UPDATE OR DELETE ON TEST_USER

for each row

BEGIN

IF INSERTING THEN

--INSERT触发

insert into TEST_USER@DBLINK_TEST values(:new.ID,:new.USER_NAME,:new.USER_PASS,:new.CREATE_TIME);

ELSIF UPDATING THEN

--UPDATE触发

UPDATE TEST_USER@DBLINK_TEST SET USER_NAME = :new.USER_NAME , USER_PASS = :new.USER_PASS WHERE ID = :new.ID;

ELSIF DELETING THEN

--DELETE触发

DELETE FROM TEST_USER@DBLINK_TEST WHERE ID = :old.ID;

END IF;

END;

5.INSERT测试(A数据库)

insert into TEST_USER values(1,'test','123456',sysdate);

insert into TEST_USER values(2,'test','123456',sysdate);

commit;

select * from TEST_USER@DBLINK_TEST;

或者登录查看B数据库看是否数据插入

6.UPDATE测试(A数据库)

UPDATE TEST_USER SET USER_NAME = 'UPDATE_TEST' , USER_PASS = 'UPDATE_TEST' WHERE ID = 1;

commit;

select * from TEST_USER@DBLINK_TEST;

7.DELETE测试(A数据库)

DELETE FROM TEST_USER WHERE ID = 1;

commit;

TEST_USER@DBLINK_TEST;