oracle9i自动捕获DML语句的方法
SQL> desc t1 Name Null? Type ---- --------
A NUMBER SQL> desc t_sql Name Null? Type --------------------------- USERNAME VARCHAR2(30) CLIENT_IP VARCHAR2(20) SQL_TEXT VARCHAR2(4000) TABLE_NAME VARCHAR2(30) OWNER VARCHAR2(30) SQL> create or replace trigger capt_sql 2 BEFORE DELETE OR INSERT OR UPDATE ON t1 3 declare 4 n number; 5 stmt varchar2(4000); 6 sql_text ora_name_list_t; 7 begin 8 n := ora_sql_txt(sql_text); 9 FOR i IN 1..n LOOP 10 stmt := stmt || sql_text(i); 11 END LOOP; 12 13 insert into t_sql(USERNAME,CLIENT_IP,SQL_TEXT,TABLE_NAME,OWNER) 14 values(user,sys_context('userenv','ip_address'),stmt,'T1','RAINY'); 15 16 end; 17 / Trigger created. SQL> insert into t1(a) values(1); 1 row created. SQL> update t1 set a = 2 where a = 1; 1 row updated. SQL> delete t1; 3 rows deleted. SQL> commit; Commit complete. SQL> select * from t_sql; USERNAME CLIENT_IP SQL_TEXT TABLE_NAME OWNER ------------------------------ ------------------------------ RAINY 10.1.30.19 insert into t1(a) values(1) T1 RAINY RAINY 10.1.30.19 update t1 set a = 2 where a =1 T1 RAINY RAINY 10.1.30.19 delete t1 T1 RAINY