记录oracle数据库库表变动 $ ]/ R2 @7 P* ^
触发器: 0 d; |( \% Q9 h; d* D
CREATE OR REPLACE TRIGGER tr_trace_ddl
( l9 [6 b& m- E3 m. y AFTER DDL ON DATABASE - R* b; h7 s0 h3 Y l: D* ]
DECLARE
$ c1 K, Q9 k5 @- { sql_text ora_name_list_t;
, L- J* w; F( L& h1 n4 u" o } state_sql ddl$trace.ddl_sql%TYPE; 5 Z- r5 o* `" ?7 q+ f3 [
BEGIN
3 i" g& s) z5 s! ]+ `1 x p- _ FOR i IN 1..ora_sql_txt(sql_text) LOOP
. V1 l) E4 J7 m$ B: t state_sql := state_sql||sql_text(i); . U+ Y. l9 s6 j1 H, Z6 M+ _2 Q
END LOOP; % ^! }) V# K. \ x+ I9 k
INSERT INTO ddl$trace(login_user,ddl_time,ip_address,audsid, 2 d7 |+ u4 x8 c9 `" D& s/ Z4 k/ {
schema_user,schema_object,ddl_sql)
3 I D6 Q* ?, Q7 e5 b8 C valueS(ora_login_user,SYSDATE,userenv(’SESSIONID’),
# i% \# {* O7 _6 y8 S/ k) h' E sys_context(’USERENV’,’IP_ADDRESS’), / L! F; ~% t/ {
ora_dict_obj_owner,ora_dict_obj_name,state_sql);
( B" i3 P+ w0 F+ l2 w6 L. U y --EXCEPTION s% I% X5 }; u n& O
--WHEN OTHERS THEN
+ A/ Z9 g3 K5 B& V3 I8 z0 m& ] --sp_write_log(’Capture DDL Excption:’||SQLERRM);
* H8 H# k4 l, |2 a8 ~" B4 c END tr_trace_ddl; |