dblink + minus实现数据增量同步试验

环境描述：
         一个汇总库库prod1，多个从库（这里试验采用一个从库pinggu），数据库用户均采用bzj，版本均为11.2.0.1。
试验需求：
         汇总库库中有一张分区表，每个分区对应一个从库，编写存储过程，实现对主库分区表增删改，定时同步到对应从库，采用dblink minus由汇总库库到从库单向同步。
步骤：
1、总库操作， 在tnsnames.ora文件下添加如下内容：
PINGGU =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1721))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pinggu)
    )
  )
2、总库创建dblink

SQL> conn / as sysdba

Connected.

SQL> create public database link pinggu using 'pinggu';

Database link created.

SQL> grant create database link to bzj;

Grant succeeded.
SQL> conn bzj/bzj

Connected.

SQL> create database link pinggu connect to bzj identified by bzj;

 Database link created.
3、总库分区表结构（不支持lob，会报错）

CREATE TABLE STAFF
   (ID VARCHAR2(200), 
  STA_NAME VARCHAR2(20), 
  SCH_NAME VARCHAR2(100)) 
   partition by list(sch_name)(
   partition haidian values('haidian'),
   partition chaoyang values('chaoyang'),
   partition qita values(default))

4、总库存储过程内容（同步到多个从库，边写多个存储过程）
Create or replace procedure staff_sync_haidian as 
Begin
Delete from staff@pinggu where id in (
Select id from 
(select * from staff partition(haidian) minus select * from staff@pinggu) a);
Commit;
 Insert into staff@pinggu select * from 
(select * from staff partition(haidian) minus select * from staff@pinggu) b;
Commit;
Delete from staff@pinggu where id in (
Select id from (
Select * from staff@pinggu minus select * from staff partition(haidian)) c);
Commit;
End;

5、在从库创建staff表
CREATE TABLE STAFF
   (ID VARCHAR2(200), 
  STA_NAME VARCHAR2(20), 
  SCH_NAME VARCHAR2(100))

6、在总库插入一条数据
INSERT INTO STAFF (ID, STA_ADDRESS, STA_NO, STA_SEX, QQ, STA_NAME, SCH_NAME) VALUES ('x3243fdfd', 'beijing', '12345678', 'man', '1198226681', 'jason', 'haidian')

7、 执行存储过程
SQL> exec staff_sync_haidian;
报错ORA-02070: database PINGGU does not support extended partition name in this context
8、检查错误：

SQL> Select id from (select * from staff partition(haidian) minus select * from staff@pinggu);
ID
--------------------------------------------------------------------------------
x3243fdfd

SQL> Delete from staff@pinggu where id in(
  2  Select id from (select * from staff partition(haidian) minus select * from staff@pinggu) a);
Delete from staff@pinggu where id in(
*
ERROR at line 1:
ORA-02070: database PINGGU does not support extended partition name in this
context

SQL> Delete from staff@pinggu where id in(
  2  Select id from (select * from staff minus select * from staff@pinggu) a);
0 rows deleted.

SQL> Insert into staff@pinggu select * from 
  2     (select * from staff partition(haidian) minus select * from staff@pinggu);
Insert into staff@pinggu select * from
*
ERROR at line 1:
ORA-02070: database PINGGU does not support extended partition name in this
context

证明远程delete、insert 不支持查询某个分区表，做如下修改：
SQL> Delete from staff@pinggu where id in(
  2  Select id from (select * from staff where sch_name='haidian' minus select * from staff@pinggu) a);
0 rows deleted.

9、修改存储过程
SQL> drop procedure staff_sync_haidian;
Procedure dropped.
SQL> Create or replace procedure staff_sync_haidian as 
  2  Begin
  3  Delete from staff@pinggu where id in (
  4  Select id from 
  5             (select * from staff where sch_name='haidian' minus select * from staff@pinggu) a);
  6  Commit;
  7   Insert into staff@pinggu select * from 
  8     (select * from staff where sch_name='haidian' minus select * from staff@pinggu) b;
  9  Commit;
 10  Delete from staff@pinggu where id in (
 11     Select id from (
 12             Select * from staff@pinggu minus select * from staff where sch_name='haidian') c);
 13  Commit;
 14  End;
 15  /
Procedure created.

SQL> exec staff_sync_haidian;
PL/SQL procedure successfully completed.

检查从库同步成功。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29193965/viewspace-1320378/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29193965/viewspace-1320378/