CREATE OR REPLACE TRIGGER trg_trace_ddl
/*
created by wangfanggen on 2005-09-05
It is traced to drop tables on the database.
when any objects had droped,the table of t_ddl_trace have recorded the detail of drop.
*/
/*
log table script.
create table t_ddl_trace
(login_user varchar2(30),
ddl_time date,
ip_address varchar2(30),
audsid varchar2(30),
TERMINAL varchar2(100),
OS_USER varchAR2(100),
SESSION_USER VARCHAR2(100),
schema_user varchar2(100),
schema_object varchar2(200),
ddl_sql varchar2(4000))
tablespace users;
*/
AFTER alter or drop or truncate on database
DECLARE
sql_text ora_name_list_t;
state_sql t_ddl_trace.ddl_sql%TYPE;
BEGIN
FOR i IN 1 .. ora_sql_txt(sql_text) LOOP
state_sql := state_sql || sql_text(i);
END LOOP;
INSERT INTO t_ddl_trace
(login_user,
ddl_time,
ip_address,
audsid,
TERMINAL,
OS_USER,
SESSION_USER,
schema_user,
schema_object,
ddl_sql)
VALUES
(ora_login_user,
SYSDATE,
sys_context('USERENV', 'IP_ADDRESS'),
userenv('SESSIONID'),
SYS_CONTEXT('USERENV', 'TERMINAL'),
SYS_CONTEXT('USERENV', 'OS_USER'),
SYS_CONTEXT('USERENV', 'SESSION_USER'),
ora_dict_obj_owner,
ora_dict_obj_name,
state_sql);
EXCEPTION
WHEN OTHERS THEN
null;
END trg_trace_ddl;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26764/viewspace-509489/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26764/viewspace-509489/