Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

To drop BIN$% table with DDL trigger enabled

707037Aug 21 2012 — edited Aug 21 2012
Hi,
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 18 2012
Added on Aug 21 2012
5 comments
443 views