How to implement all library DDL operations in trigger set

Today, the client said a need that I feel interesting: a table on one library records the DDL operations of all libraries by creating tables and triggers on one library, and logging to remote tables on other libraries via dblink+ synonyms + triggers for DDL operations. He wrote a trigger, but there was a mistake, Want me to help solve. Together with our efforts, we solved the problem of the trigger error in the library of Dblink synonyms. I'm here to test using 10g libraries as a library for storing DDL records for all libraries, and 11g libraries as a library for inserting DDL operations Records through Dblink.

Operating in the 10g database library

1. Create a record DDL action sheet

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),

Os_user VARCHAR2 (40),

One ddl_sql varchar2 (4000));

Table created.

2. Create triggers

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);

Ten str_session V$session%rowtype;

Begin

N: = Ora_sql_txt (Sql_text);

The I in 1. N Loop

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

End Loop;

Dbms_application_info. Read_module (V_module, v_action);

INSERT into Chf.t_ddl_audit

(Db_name,

Login_user,

Ddl_time,

IP_Address,

Audsid,

Schema_user,

Schema_object,

Login_tool,

Os_user,

Ddl_sql)

VALUES

Sys_context (' USERENV ', ' db_name '),

Ora_login_user,

Sysdate,

Sys_context (' USERENV ', ' ip_address '),

Userenv (' SESSIONID '),

Ora_dict_obj_owner,

Ora_dict_obj_name,

V_module,

Notoginseng sys_context (' Userenv ', ' os_user '),

STR_STMT);

exception

When No_data_found Then

null;

The end;

43/

Trigger created.

3. Test triggers

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

Operating in a 11g database

1. Create Dblink and synonyms

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. Create triggers for the first time

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);

Ten str_session V$session%rowtype;

Begin

N: = Ora_sql_txt (Sql_text);

The I in 1. N Loop

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

End Loop;

Dbms_application_info. Read_module (V_module, v_action);

INSERT into T_ddl_audit

(Db_name,

Login_user,