Protect Oracle Database Object From Drop Operation
Reference:
http://xiaoruanjian.iteye.com/blog/879083
Create a system trigger can protect table from drop operation
Example 1:
CONNECT SYSTEM/<password> CREATE OR REPLACE TRIGGER "TRG_DENY_DROP" BEFORE DROP ON DATABASE BEGIN IF ORA_DICT_OBJ_OWNER='<the schema to protect>' AND ORA_LOGIN_USER<>'<super user who can drop>' AND ORA_DICT_OBJ_TYPE in ('TABLE', 'VIEW', 'INDEX') AND ORA_DICT_OBJ_NAME NOT LIKE '<the table which can be drop>' THEN RAISE_APPLICATION_ERROR(-20010,'you cannot drop this table.'); END IF; END TRG_DENY_DROP;
Example 2:
Reference:
http://wenku.baidu.com/view/0c0598cda1c7aa00b52acb78.html
http://searchoracle.techtarget.com/tip/Protect-tables-from-getting-dropped-by-mistake
create table protect_object as select obj ect_name , owner from dba_objects where object_type in ('TABLE','INDEX','VIEW','PROCEDURE','SYNONYM'); create or replace trigger trg_deny_drop_ubisp before drop on database begin if ora_dict_obj_owner='<the schema should be protected>' then raise_application_error(-20010,'nonono,you cannot drop any table.'); end if; end trg_deny_drop_ubisp; create or replace trigger trigger_protect_object before drop on database declare v_object_name varchar2(128); v_owner varchar2(128); begin select object_name into v_object_name from protect_object where object_name=ora_dict_obj_name and owner=login_user; if SQL% found then raise_application_error(-20001,'you cannot drop projected table!!'); end if; exception when no_data_found then null; when too_many_rows then raise_application_error(-20002,'remove duplicate entry from project_object table'); end; /
http://docs.oracle.com/cd/A97630_01/appdev.920/a96590/adg14evt.htm