[Oracle 11g r2(11.2.0.4.0)]sql实现ddl记录日志

打算做一个ddl触发器， 对于所有的ddl的操作进行记录，同时 针对不允许删除的表不允许删除 编写如下。

—ddl日志
–drop table sys_ddl_log;
create table sys_ddl_log (
c_ora_sysevent varchar2(100),
c_login_user varchar2(100),
c_dictionary_obj_type varchar2(100),
c_dictionary_obj_name varchar2(100),
c_dictionary_obj_owner varchar2(100),
c_oper_date date
);
— 受保护的对象
drop table sys_protect_user_obj;
create table sys_protect_user_obj(
c_obj_owner varchar2(100),
c_obj_type varchar2(100),
c_obj_name varchar2(100)
);
INSERT INTO sys_protect_user_obj (c_obj_owner, c_obj_type ,c_obj_name )
select T.OWNER,T.OBJECT_TYPE,T.OBJECT_NAME from dba_objects t where t.owner in (‘SYSUSER’) AND OBJECT_TYPE IN (‘TABLE’,’VIEW’,’MATERIALIZED VIEW’,’PROCEDURE’);
COMMIT;

—记录ddl的事件
create or replace trigger trig_log_afterddl
after ddl on database
declare
COU NUMBER;

begin
if ora_sysevent=’DROP’ then
select COUNT(*) into COU from sys_protect_user_obj where
c_obj_owner = ora_dict_obj_owner and
c_obj_name = ora_dict_obj_name and
c_obj_type=ora_dict_obj_type ;
if COU>0 then
raise_application_error(-20001,’该对象受到保护，禁止删除。请联系管理员！’);
end if;
else

insert into sys_ddl_log(c_ora_sysevent ,c_login_user, c_dictionary_obj_type, c_dictionary_obj_name ,c_dictionary_obj_owner ,c_oper_date )
values(ora_sysevent , ora_login_user,ora_dict_obj_type, ora_dict_obj_name ,ora_dict_obj_owner,sysdate);

end if ;

end;