转载自:http://xzh2000.itpub.net/post/96/222277
如果要维护多套开发与测试的数据库环境，如果schema变动很频繁，那dba的工作量也就非常可观，稍一不慎，就会给开发以及测试有不好的影响，所以同步开发与测试环境的是必不可少的，d.c.b.a曾经开发过一个比较并同步schema的工具，但需要手工执行去同步，偶这次介绍的是通过job+procedure来完成的。
1 在源库上创建捕获DDL的触发器与表
a 创建表
create table manager.ddl$trace
(
login_user varchar2(30),
ddl_time date,
program varchar2(64),
sid number,
schema_user varchar2(30),
schema_object varchar2(30),
ddl_sql varchar2(4000),
id number not null,
status number,
errm varchar2(4000)
)
b 创建触发器
create or replace trigger ddl_trigger
before ddl on database
/*
--last modify date:2006-10-18
--last modifier:yekai
--desc:replication ddl to test database
*/
declare
n number;
str_stmt varchar2(4000);
sql_text ora_name_list_t;
l_trace number;
l_sid number;
str_session v$session%rowtype;
begin
--get ddl script
n := ora_sql_txt(sql_text);
for i in 1..n loop
str_stmt := substr(str_stmt||sql_text(i), 1, 3000);
end loop;
--get modify status and permission
select count(*) into l_trace
from dual
where sys_context('userenv','ip_address') is not null
and lower(str_stmt) not like 'truncate% purge snapshot log%'
and lower(str_stmt) not like 'alter% compile%'
and lower(str_stmt) not like 'alter% session%'
and lower(str_stmt) not like '%create%'
and lower(str_stmt) not like '%alter%'
and lower(str_stmt) not like '%drop%'
and lower(str_stmt) not like '%grant%';
--get session information
select * into str_session
from v$session
where sid = (select sid from v$mystat where rownum = 1);
--if not permit then alert
if l_trace > 0 then
--write alert file
sys.dbms_system.ksdwrt(2,'ora-20001:user:'||ora_login_user||',time:'||to_char(sysdate,'yyyymmdd hh24:mi:ss')||',program:'||str_session.program||',ip:'||sys_context('userenv','ip_address')||',object:'||ora_dict_obj_name||',ddl: '||str_stmt);
--raise exception to user
raise_application_error(-20001,'you can not execute ddl on this object except on the local machine');
else
--write information to table
insert into manager.ddl$trace(login_user,ddl_time,program,sid,schema_user,schema_object,ddl_sql,id)
values(ora_login_user,sysdate,str_session.program,l_sid,ora_dict_obj_owner,ora_dict_obj_name,str_stmt,seq_test.nextval);
end if;
exception
when others then
--raise exception to user
raise;
end;
2 在目标库上创建执行动态SQL的过程
create or replace procedure sp_executeddl(v_sql in varchar2)
authid current_user as
begin
execute immediate v_sql;
end;
3 在源库上创建database link
create database link lnk_testdbc connect to test identified by test using 'testdbc';
3 在源库上创建执行同步的过程
create or replace procedure sp_syncddl
as
v_errm varchar2(256);
begin
for i in (select id, ddl_sql from manager.ddl$trace where status is null)
loop
begin
sp_executeddl@lnk_testdbc(i.ddl_sql);
exception
when others then
v_errm := substr(sqlerrm, 1,256);
update manager.ddl$trace
set errm = v_errm,
status = -1
where id = i.id;
end;
update manager.ddl$trace
set status = 1
where id = i.id and status is null;
end loop;
commit;
end sp_syncddl;
4 在源库上创建同步schema的job
var jobid number;
exec dbms_job.submit(:jobid, 'sp_syncddl;', sysdate, 'sysdate+5/1440');