oracle跨库实现ddl,如何实现trigger集中记录所有库ddl操作

今天客户说了一个我感觉有意思的需求:在一个库上的一张表记录所有库的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,

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> create table t_xff as select * from dba_objects where rownum<10;

create table t_xff as select * from dba_objects where rownum<10

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-02070: database  does not support  in this context

ORA-06512: at line 15

出现ORA-02070错误,估计是类此sys_context(‘userenv’, ‘os_user’)导致。

4.第二次创建触发器

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    v_db_name   varchar2(50);

11    v_ip_addr   varchar2(50);

12    v_os        varchar2(50);

13    v_session_id varchar2(50);

14    str_session v$session%rowtype;

15  begin

16    n := ora_sql_txt(sql_text);

17    for i in 1 .. n loop

18      str_stmt := substr(str_stmt || sql_text(i), 1, 3000);

19    end loop;

20    dbms_application_info.READ_MODULE(v_module, v_action);

21    v_db_name :=sys_context('USERENV', 'db_name');

22    v_ip_addr :=sys_context('USERENV', 'IP_ADDRESS');

23    v_os:=sys_context('userenv', 'os_user');

24    v_session_id:=userenv('SESSIONID');

25    INSERT INTO t_ddl_audit

26      (db_name,

27       login_user,

28       ddl_time,

29       ip_address,

30       audsid,

31       schema_user,

32       schema_object,

33       login_tool,

34       os_user,

35       ddl_sql)

36    VALUES

37      (v_db_name,

38       ora_login_user,

39       SYSDATE,

40       v_ip_addr,

41      v_session_id,

42       ora_dict_obj_owner,

43       ora_dict_obj_name,

44       v_module,

45       v_os,

46       str_stmt);

47  exception

48    when no_data_found then

49      null;

50  end;

51  /

Trigger created.

5.继续测试触发器

SQL> drop table t3;

drop table t3

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-02069: global_names parameter must be set to TRUE for this operation

ORA-06512: at line 23

根据ORA-02069,查询资料发现是通过dblink插入数据使用了变量和常量的方式混合使用导致该错误,修改触发器全部使用变量方式

6.第三次创建触发器

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    v_db_name   varchar2(50);

11    v_ip_addr   varchar2(50);

12    v_os        varchar2(50);

13    v_session_id varchar2(50);

14    v_loginuser    varchar2(50);

15     v_obj_name varchar2(50);

16    v_owner    varchar2(50);

17    str_session v$session%rowtype;

18  begin

19    n := ora_sql_txt(sql_text);

20    for i in 1 .. n loop

21      str_stmt := substr(str_stmt || sql_text(i), 1, 3000);

22    end loop;

23    dbms_application_info.READ_MODULE(v_module, v_action);

24    v_db_name :=sys_context('USERENV', 'db_name');

25    v_ip_addr :=sys_context('USERENV', 'IP_ADDRESS');

26    v_os:=sys_context('userenv', 'os_user');

27    v_session_id:=userenv('SESSIONID');

28    v_loginuser:= ora_login_user;

29    v_owner:=ora_dict_obj_owner;

30    v_obj_name:=ora_dict_obj_name;

31    INSERT INTO t_ddl_audit

32      (db_name,

33       login_user,

34       ddl_time,

35       ip_address,

36       audsid,

37       schema_user,

38       schema_object,

39       login_tool,

40       os_user,

41       ddl_sql)

42    VALUES

43      (v_db_name,

44       v_loginuser,

45       SYSDATE,

46       v_ip_addr,

47      v_session_id,

48       v_owner,

49       v_obj_name,

50       v_module,

51       v_os,

52       str_stmt);

53  exception

54    when no_data_found then

55      null;

56  end;

57  /

Trigger created.

7.测试触发器

SQL> create table t_xff11 as select * from dba_tables where rownum<10;

Table created.

SQL> select db_name,login_user,ddl_sql from t_ddl_audit;

DB_NAME                        LOGIN_USER

------------------------------ ------------------------------

DDL_SQL

-----------------------------------------------------------------

ora11g                         CHF

create table t_xff11 as select * from dba_tables where rownum<10

XFF                            CHF

create table t_xff as select * from dba_tables where rownum=1

补充说明

这个方案个人感觉是一个实验室中的方案,在实际的生成环境中很难应用上

1.trigger记录ddl操作本身效率不高

2.如果某个库不能访问存储ddl操作的表的数据库,将导致该数据库所有ddl操作hang住,从而可能使得该数据库hang住的风险.