Oracle 自动同步数据脚本

这篇文章是之前自己在公司的一篇技术分享，搬过来就不提供代码的下载了

前段时间在处理一个生产异常的时候发现，我们的测试数据库和仿真数据库已经很久都没有同步生产上的数据了。

我们开发人员在处理异常的时候往往要模拟一条数据来进行调试，若遇到需要大量接近生产的基础数据进行调试的时候就比较痛苦了。

而目前遇到这种情况则需要实施人员到生产数据库备份数据，通过Oracle导出将数据导成dmp的格式，再重新导入到测试数据库或者仿真数据库进行调试。

但是这个过程中实施人员容易出错，而且效果不理想（因为有些blob字段无法导入到系统中，还有表空间不一致也影响导入）。鉴于这种情况我决定写一个同步的脚本对表进行同步代替这一大串操作。

这次的存储过程是通过Oracle的Package（包头）和 Package Body（包体）来实现同步的。

其中TIMSSPRD_SYNC是数据库同步入口，而TIMSS_TABLE是同步表方法的入口。

考虑使用Package的原因在于：

• 对于程序员来说，Package/Package Body的写法更贴近于面向对象的开发理念（个人认为跟Java中的接口和接口实现类如出一辙）；
• Package内的方法可以重载，也可以存在多个同名的方法根据参数的个数或者参数的类型找到正确的方法；
• Package可以被其他用户调用，是全局的方法；
• Package既然是全局的，那当然也可以被授权咯（可以按需分批，那些用户可以用，那些用户不可以用）；

先打开TIMSSPRD_SYNC，里面有一个TIMSS_SYNC_PRO的方法，里面有四个参数分别是

• OPER_SORT（同步类型） ：脚本将根据填入的内容去更新指定的类型信息

• OPER_TYPE（同步方式） ：是include（包含）还是exclude（排除）

• OPER_TABLE（同步表名） ：其实这个字段不单单是表名（因为现在只有表），这里应该是同步对象的名称

• OPER_PREFIX（同步前缀） ：同理，这里应该是同步对象的前缀

如果什么参数都不填的话会执行全部类型的更新（如果全部更新的话还不如用Dmp的方法，比较简单）。

若填了参数则按照同步信息去判断需要调用的方法。

再来的就是TIMSS_TABLE，里面就是真正的执行方法共四个，分别是：

ORGANIZE_DATA（整理数据）

CREATE_AND_BAK_TABLE（创建与备份表）

CREATE_TABLE_EXEC（执行创建表）

DROP_TABLE（表删除）

这四个方法的具体内容就不在这里面说了，在Package Body里面已经写得相当清楚，有兴趣的同事可以去看看不明白的可以来问我。

在编写TIMSS_TABLE中用到了6个有用的存储过程知识想分享一下：

1 用到了一个自定义的函数去对参数内容进行分割，因为Oracle本来是没有自带分割函数的。

为了方便使用，于是在网上抄了一个自定义函数 SPLITSTR(需要分割的字符串，分割字符)

这个函数最后是以管道（Pipelined）的方式返回，用一个虚拟的Table将其内容接住

所以在上面可以看到使用的格式是

select column_value from table( splitstr( xxx , yyy )) ;

2 在存储过程的方法体中依然能够用

Begin        执行方法...        Exception When Others Then        异常处理信息... End；

这个格式的处理方式来模拟Java中的Try Catch方法。

3 使用动态Sql的时候采用Using的方法进行赋值，这样即使赋值的变量内容中含有特殊字符也不会抛错。

4 使用Dbms_Lock.Sleep方法来做睡眠操作，就像Java线程中的Sleep那样。

有时候有些操作是有分先后顺序的，而存储过程处理太快很容易就发生冲突，这个时候就需要用Dbms_Lock.Sleep方法让它睡眠个0.01秒，让上面的完成了再做下面的（不过这个Dbms_Lock的包需要管理员权限的人员开放给需要的用户才可以使用，比较麻烦 ）

5 使用 Drop Table的时候加上Purge，可以减少表空间的消耗。

因为Oracle是有闪回（FlashBack）的功能的，平常我们删除的东西其实还是会存放在回收站（Recycle Bin）里面不会立即清除的，方便闪回的时候恢复数据。所以一般的Drop Table虽然表是已经删除了，但是表空间是不会释放的。

因此这次的存储过程我们就用一个Purge来让这个表彻底删除（这里的处理逻辑是先创建好目标表后删除掉备份表）同时释放表空间。

平常建议不要使用这种方式，这种方式删除后不能恢复非常危险需要慎用。

6 最后要说一下的是大家在用PL/SQL的时候应该注意一点就是Brower里面应该是默认为“My Objects“。

好几次看到同事们的PL/SQL一打开就是“All Objects”这样是非常危险的。

如果用DBA的账号登陆你会发现打开下面树的每个节点都可以看到系统级别的文件像是什么 SYS.XXX开头的文件。

万一不小心给删除掉了，那整个实例就完蛋了。建议每位同事都将自己的PL/SQL设置一下，哪怕你现在不需要用到DBA的账号也好，养成一个好习惯是没错的。

设置可以通过 Tools->Preferences->User Interface->Browser 点击Filters…按钮去进行设置

这个存储过程其实还存在很多问题，譬如大数据量同步的时候就不能使用这个存储过程了、DBlink本身也存在lob数据类型同步问题、存储过程内没有做同步信息记录（这个需要的话要后续跟进）……