Oracle DDL 触发器

--1 在源库中创建捕获DDL的触发器与表
--a--创建表
create table ddl$trace
(login_user varchar2(30),
ddl_time date,
program varchar2(64),
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_triger
       before ddl on 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');