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

advertisements

_____________________________________________________________________________________________________________________

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

_____________________________________________________________________________________________________________________

Website Stats