实现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住的风险.

 

 

（以上内容摘于网络，如有侵权，请告之，将第一时间删除）