（摘）DML 语句的监控

网上找到的，记下来备用。

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 create 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;

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7425608/viewspace-890858/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7425608/viewspace-890858/