日期:2015-06-28 00:00:00 来源: IT猫扑网
触发器:
CREATE OR REPLACE TRIGGER tr_trace_ddl
AFTER DDL ON DATABASE
DECLARE
sql_text ora_name_list_t;
state_sql 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 ddl$trace(login_user,ddl_time,ip_address,audsid,
schema_user,schema_object,ddl_sql)
valueS(ora_login_user,SYSDATE,userenv('SESSIONID'),
sys_context('USERENV','IP_ADDRESS'),
ora_dict_obj_owner,ora_dict_obj_name,state_sql);
--EXCEPTION
--WHEN OTHERS THEN
--sp_write_log('Capture DDL Excption:'||SQLERRM);
END tr_trace_ddl;
日志表结构:
create table DDL$TRACE
(
LOGIN_USER VARCHAR2(10),
DDL_TIME DATE,
IP_ADDRESS VARCHAR2(15),
AUDSID VARCHAR2(20),
SCHEMA_USER VARCHAR2(10),
SCHEMA_OBJECT VARCHAR2(50),
DDL_SQL CLOB
)
相关文章
相关下载
网友评论