ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DDL 触发器 监控

DDL 触发器 监控

原创 Linux操作系统 作者:phigon 时间:2019-02-20 15:33:04 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2002-10-29

  • 博文量
    35
  • 访问量
    27442