今天客户说了一个我感觉有意思的需求:在一个库上的一张表记录所有库的ddl操作,实现方式：在一个库上建立表和触发器,其他库上通过dblink+同义词+触发器实现ddl操作记录到远程的表中.他当时写了一个触发器,但是有错误,想让我协助解决.在我们的一起努力下,解决了该触发器在dblink同义词的库上出错的问题.我这里测试使用的是10g的库做为存储所有库的ddl记录的库,11g库做为一个通过dblink插入ddl操作记录的库.
在10g数据库库中操作
1.创建记录ddl操作表
SQL> conn chf/xifenfei
Connected.
SQL> create table t_ddl_audit(
2 db_name varchar2(30),
3 login_user varchar2(30),
4 ddl_time date,
5 ip_address varchar2(20),
6 audsid varchar2(20),
7 schema_user varchar2(30),
8 schema_object varchar2(40),
9 login_tool varchar2(40),
10 os_user varchar2(40),
11 ddl_sql varchar2(4000));
Table created.
2.创建触发器
SQL> create or replace trigger tri_ddl_audit
2 before ddl on database
3 declare
4 n number;
5 str_stmt varchar2(4000);
6 sql_text ora_name_list_t;
7 l_trace number;
8 v_module varchar2(50);
9 v_action varchar2(50);
10 str_session v$session%rowtype;
11 begin
12 n := ora_sql_txt(sql_text);
13 for i in 1 .. n loop
14 str_stmt := substr(str_stmt || sql_text(i), 1, 3000);
15 end loop;
16 dbms_application_info.READ_MODULE(v_module, v_action);
17 INSERT INTO chf.t_ddl_audit
18 (db_name,
19 login_user,
20 ddl_time,
21 ip_address,
22 audsid,
23 schema_user,
24 schema_object,
25 login_tool,
26 os_user,
27 ddl_sql)
28 VALUES
29 (sys_context('USERENV', 'db_name'),
30 ora_login_user,
31 SYSDATE,
32 sys_context('USERENV', 'IP_ADDRESS'),
33 userenv('SESSIONID'),
34 ora_dict_obj_owner,
35 ora_dict_obj_name,
36 v_module,
37 sys_context('userenv', 'os_user'),
38 str_stmt);
39 exception
40 when no_data_found then
41 null;
42 end;
43 /
Trigger created.
3.测试触发器
SQL> conn chf/xifenfei
Connected.
SQL> create table t_xff as select * from dba_tables where rownum=1;
Table created.
SQL> select db_name,login_user,ddl_sql from t_ddl_audit;
DB_NAME LOGIN_USER
------------------------------ ------------------------------
DDL_SQL
-----------------------------------------------------------------
XFF CHF
create table t_xff as select * from dba_tables where rownum=1
在11g数据库中操作
1.创建dblink和同义词
SQL> create database link "ora10g_dblink"
2 connect to chf
3 identified by "xifenfei"
4 using 'ora10g';
Database link created.
SQL> create synonym t_ddl_audit for t_ddl_audit@ora10g_dblink;
Synonym created.
2.第一次创建触发器
SQL> create or replace trigger tri_ddl_audit
2 before ddl on database
3 declare
4 n number;
5 str_stmt varchar2(4000);
6 sql_text ora_name_list_t;
7 l_trace number;
8 v_module varchar2(50);
9 v_action varchar2(50);
10 str_session v$session%rowtype;
11 begin
12 n := ora_sql_txt(sql_text);
13 for i in 1 .. n loop
14 str_stmt := substr(str_stmt || sql_text(i), 1, 3000);
15 end loop;
16 dbms_application_info.READ_MODULE(v_module, v_action);
17 INSERT INTO t_ddl_audit
18 (db_name,
19 login_user,