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