To drop BIN$% table with DDL trigger enabled
707037Aug 21 2012 — edited Aug 21 2012Hi,
In our environment we have enabled BEFORE DDL ON DATABASE trigger to avoid any ddl operation by user but due to a bug (Bug.13967494 (11) RECYCLEBIN INTERNAL OPERATION FIRES DDL TRIGGER) database schemas are trying to drop BIN% tables so i need to add a exception to allow ddl activity over the table's name starting with BIN$ in the trigger.
Is it Possible
DDL TRIGGER :
**************
CREATE OR REPLACE TRIGGER "SYS"."NO_DDL_TRIG"
BEFORE DDL
ON DATABASE
BEGIN
IF (
ora_login_user in ('SYS','OICLPOC')
or
(TO_CHAR(SYSDATE,'hh24:mi') between '03:00' and '06:00')
)
THEN
INSERT INTO isys.DDL_LOG
(ddl_event, ddl_obj_owner,
ddl_obj_type, ddl_obj_name,
ddl_sql_text, ddl_user,
ddl_dt
)
VALUES (
ora_sysevent, ora_dict_obj_owner,
ora_dict_obj_type, ora_dict_obj_name,
'Success',
ora_login_user,
SYSDATE
);
else
INSERT INTO isys.DDL_LOG
(ddl_event, ddl_obj_owner,
ddl_obj_type, ddl_obj_name,
ddl_sql_text, ddl_user,
ddl_dt
)
VALUES (
ora_sysevent, ora_dict_obj_owner,
ora_dict_obj_type, ora_dict_obj_name,
'Failed Attempts' , ora_login_user,
SYSDATE
);
RAISE_APPLICATION_ERROR(-20009,'YOU CANT DO DDL, THIS TIME?.. Contact DBA');
END IF;
END no_ddl_trig;
/
Thanks and Regards,
Satish Abraham.J