记录oracle数据库库表变动
`3 ]+ ?% B1 d6 v触发器: , v9 E- C2 ]$ V4 `
CREATE OR REPLACE TRIGGER tr_trace_ddl
8 X/ Q" _/ P6 A AFTER DDL ON DATABASE
6 w) O; `' F5 F5 i; R4 d DECLARE : N: A5 J0 o. b" _
sql_text ora_name_list_t; # [: H3 K. c D9 w9 `. f% ]
state_sql ddl$trace.ddl_sql%TYPE; $ Q) M& j. U9 E1 o0 ?
BEGIN % B6 n3 ?0 N+ V2 {* l: m
FOR i IN 1..ora_sql_txt(sql_text) LOOP ' @, _. D/ L' c4 |/ ]
state_sql := state_sql||sql_text(i); : N8 d/ J" i' y4 W
END LOOP; % r5 t6 m5 e9 v# m: i& n
INSERT INTO ddl$trace(login_user,ddl_time,ip_address,audsid, % p% g0 d. `0 Z( c* v
schema_user,schema_object,ddl_sql) 1 k. f! Y- q' H6 d
valueS(ora_login_user,SYSDATE,userenv(’SESSIONID’),
- |" H' V, ~& J sys_context(’USERENV’,’IP_ADDRESS’),
! a) x0 d1 ^" k; d! Y ora_dict_obj_owner,ora_dict_obj_name,state_sql); # X9 d- e) g- L
--EXCEPTION }6 q0 w' E1 b! B5 e- R* ?
--WHEN OTHERS THEN
% E( C% Y: a0 p) w' s! L --sp_write_log(’Capture DDL Excption:’||SQLERRM);
8 j* [( z" `0 s' a END tr_trace_ddl; |