How to Avoid / Stop /Restrict the Dropping of User Schema by Mistake from Oracle Database?

Suppose multiple DBAs are working on simultaneously in different databases, there are chances of dropping the user or schema by mistake. Eg: Wrongly dropping the user schemas in similarity of the names etc. Suppose if the schema is having objects it will throw the error if you are not using the cascade option. If you use cascade option with drop command it will delete the user without asking for any kind of confirmation. It can be avoided by using the following trigger which can be created under sys/system schema.

Create Or Replace Trigger AvoidSchemaDrop
Before Drop On Database
Declare
    BEGIN
     If Ora_Dict_Obj_Name  In ('STHOMAS')   
    THEN
        Raise_Application_Error(-20015,'Schema or User '||Ora_Dict_Obj_Name ||' Cannot be dropped!'); 
    END IF;
End;
/
Trigger created.
You can change the schema names (marked in Red) as per your requirement.
SQL>drop user sthomas;
drop user sthomas
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20015: Schema or User STHOMAS Cannot be dropped!
ORA-06512: at line 5


DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google