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



The Eucharistic Miracles of the World
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
     If Ora_Dict_Obj_Name  In ('STHOMAS')   
        Raise_Application_Error(-20015,'Schema or User '||Ora_Dict_Obj_Name ||' Cannot be dropped!'); 
    END IF;
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


Website Stats


Post a Comment


Oracle (629) Script (86) General (77) Unix (47) Blog (23) Technology (19) gadget (6) games (6) Business (3) OCI (3) SQL* Loader (3) Datapump (2) Copyright 2011-23 All Rights Reserved | Site Map | Contact | Disclaimer