1. 首页
  2. IT资讯

用户级数据库结构同步方案(测试版)

工作需要同步两个用户的数据库结构,故写了一个简单的数据库结构同步方案,主要通过触发器+存储过程实现,目前还不完善

/*源数据库:发出DDL语句的数据库目标数据库:需要与被源数据库结构同步的数据库注意:1)需要显示授权,如create table ,create index等,最好把用户拥有的角色对应的系统权限都显式授权2)对由系统自动生成的约束的修改和删除未实现同步

*/

/*源数据库:发出DDL语句的数据库目标数据库:需要与被源数据库结构同步的数据库注意:1)需要显示授权,如create table ,create index等,最好把用户拥有的角色对应的系统权限都显式授权2)对由系统自动生成的约束的修改和删除未实现同步

*/

–在源数据库建立连接到目标数据库的DBLINK———————————————————————————-create database link LINK_NEW_SUKconnect to SUK identified by SUKusing ‘new’;

———————————————————————————— –在目标数据库的相关用户下建立存储DDL语句的表create table T_DDL ( DDL_ID NUMBER primary key,DDL_SQL VARCHAR2(4000) NOT NULL,DDL_SYSEVENT VARCHR2(100),DDL_OBJECT_TYPE VARCHAR2(100),DDL_OBJECT_NAME VARCHAR2(100))

————————————————————————————–在源数据库和目标数据库都建立错误记录表create table t_sp_err(id number primary key,procedure_name varchar2(200),err_code number,err_msg varchar2(400),inDate date default sysdate,v_sql varchar2(4000),V_SYSEVENT VARCHAR2(100), V_OBJECT_TYPE VARCHAR2(100),V_OBJECT_NAME VARCHAR2(100));

—————————————————————————————在源数据库和目标数据库都建立序列create sequence SEQ_T_SP_ERRminvalue 1maxvalue 9999999999999999999start with 1increment by 1cache 20;

——————————————————————在源数据库建立序列,只要用来表示DDL的执行顺序,否则会出错create sequence SEQ_T_DDL_DDL_IDminvalue 1maxvalue 9999999999999999999start with 1increment by 1cache 20;

—————————————————————————————–在目标数据库建立存储过程,执行DDL语句

create or replace procedure p_execute Is/*扫描T_DDL表,动态执行SQL,同步数据库结构*/l_errnum Number;l_errmsg Varchar2(4000);l_productname Varchar2(200):=’p_execute’;l_sql Varchar2(4000);l_event Varchar2(100);l_object_type Varchar2(100);l_object_name Varchar2(100);begin For c_sql In (select ddl_event,ddl_object_type,decode(instr(upper(ddl_sql),’TABLESPACE’),0,ddl_sql,substr(ddl_sql,1,instr(upper(ddl_sql),’TABLESPACE’)-1)) ddl_sql from t_ddl) Loopl_sql:=c_sql.ddl_sql;l_event:=c_sql.ddl_event;l_object_type:=c_sql.ddl_object_type;l_object_name:=ora_dict_obj_name;If (l_object_type=’TABLE’ Or l_object_type=’INDEX’ )And (instr(upper(l_sql),’SYS_’)>0) Then–如果修改表的约束(约束名称和对应的索引有系统自动生成,则不作处理 Null;ElseBeginexecute immediate c_sql.ddl_sql;ExceptionWhen Others Thenl_errnum:=Sqlcode;l_errmsg:=Sqlerrm;Insert Into t_sp_err Values(SEQ_T_SP_ERR.Nextval,l_productname,l_errnum,l_errmsg,Sysdate,l_sql,l_event,l_object_type,l_object_name);Commit;End;End If;end loop; execute immediate ‘truncate table t_ddl’;end p_execute;—————————————————————————————在源数据库建立存储过程,得到DDL语句插入到远程表create or replace procedure p_sync_schema(p_sql Varchar2) Is/*插入远程数据表,然后调用远程存储过程执行DDL*/pragma AUTONOMOUS_TRANSACTION;–设置自治事务,避免错误ORA_04092l_errnum Number;l_errmsg Varchar2(4000);l_productname Varchar2(200):=’p_sync_schema’;l_event Varchar2(100);l_object_type Varchar2(100);l_object_name Varchar2(100);Beginl_event:=ora_sysevent;l_object_type:=ora_dict_obj_type;l_object_name:=ora_dict_obj_name;insert into t_ddl@link_new_suk values(SEQ_T_DDL_DDL_ID.nextval@link_new_suk,p_sql,l_event,l_object_type,l_object_name);p_execute@link_new_suk; ExceptionWhen Others Thenl_errnum:=Sqlcode;l_errmsg:=Sqlerrm; Insert Into t_sp_err Values(SEQ_T_SP_ERR.Nextval,l_productname,l_errnum,l_errmsg,Sysdate,’t’,l_event,l_object_type,l_object_name); Commit; end p_sync_schema;—————————————————————————————————————-建立用户DDL触发器CREATE OR REPLACE TRIGGER tri_ddl AFTER ddl ON suk.schema DECLARE sql_text ora_name_list_t; state_sql Varchar2(4000); l_errnum Number;l_errmsg Varchar2(4000);BEGIN FOR i IN 1..ora_sql_txt(sql_text) LOOP state_sql := state_sql||sql_text(i); END LOOP; p_sync_schema(state_sql);

EXCEPTION WHEN OTHERS THEN l_errnum:=Sqlcode;l_errmsg:=Sqlerrm;dbms_output.put_line(l_errnum||’£o’||l_errmsg);END tr_ddl;

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

主题测试文章,只做测试使用。发布者:布吉卡,转转请注明出处:http://www.cxybcw.com/195697.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code