使用者級資料庫結構同步方案（測試版）

工作需要同步兩個使用者的資料庫結構，故寫了一個簡單的資料庫結構同步方案，主要透過觸發器+儲存過程實現，目前還不完善

/*
源資料庫：發出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 values();
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;