ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 用户级数据库结构同步方案(测试版)

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

原创 Linux操作系统 作者:space6212 时间:2019-07-20 20:00:02 0 删除 编辑

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

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


*/


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


*/

--在源数据库建立连接到目标数据库的DBLINK
----------------------------------------------------------------------------------
create database link LINK_NEW_SUK
connect to SUK identified by SUK
using '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_ERR
minvalue 1
maxvalue 9999999999999999999
start with 1
increment by 1
cache 20;

----------------------------------------------------------------
--在源数据库建立序列,只要用来表示DDL的执行顺序,否则会出错
create sequence SEQ_T_DDL_DDL_ID
minvalue 1
maxvalue 9999999999999999999
start with 1
increment by 1
cache 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) Loop
l_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;
Else
Begin
execute immediate c_sql.ddl_sql;
Exception
When Others Then
l_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_04092
l_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);
Begin
l_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;
Exception
When Others Then
l_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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    245
  • 访问量
    166265