oracle 表发生了变化,记载oracle数据库库表变化

触发器：

CREATE OR REPLACE TRIGGER tr_trace_ddl

AFTER DDL ON DATABASE

DECLARE

sql_text ora_name_list_t;

state_sql ddl$trace.ddl_sql%TYPE;

BEGIN

FOR i IN 1..ora_sql_txt(sql_text) LOOP

state_sql := state_sqlsql_text(i);

END LOOP;

INSERT INTO ddl$trace(login_user,ddl_time,ip_address,audsid,

schema_user,schema_object,ddl_sql)

valueS(ora_login_user,SYSDATE,userenv('SESSIONID'),

sys_context('USERENV','IP_ADDRESS'),

ora_dict_obj_owner,ora_dict_obj_name,state_sql);

--EXCEPTION

--WHEN OTHERS THEN

--sp_write_log('Capture DDL Excption:'SQLERRM);

END tr_trace_ddl;

日志表结构：

create table DDL$TRACE

(

LOGIN_USER    VARCHAR2(10),

DDL_TIME      DATE,

IP_ADDRESS    VARCHAR2(15),

AUDSID        VARCHAR2(20),

SCHEMA_USER   VARCHAR2(10),

SCHEMA_OBJECT VARCHAR2(50),

DDL_SQL       CLOB

)