Drop a user in Oracle Database.

Being a database administrator entails creating and dropping a user as one of the most frequent tasks that you have to perform. I have already explained how to create user using SQL Create user StatementSQL Developer and Oracle Enterprise manager

Having said that, today I will tell you about how to drop a user when it is connected to the database as well as when it is not connected to the database.

To Drop a user you will either need a drop user system privilege or SysDBA privilege (Log on to the database using Sys user with SysDba privileges)

How to drop a user when it is not connected to the database

Suppose you have a user by the name of John and you want to drop it. To drop this user you can use DROP USER DDL statement.

For example 

DROP   USER   John;

The above statement will drop the user John. This is the situation when user does not own any objects. By objects I mean tables, indexes, view etc. In case your user owns these objects then database compiler will raise an error and will not allow you to drop the user. In this situation you will have to drop the user with cascade option.

For example, Say user john has tables, indexes and several other objects and you want to drop it. In this case you will have to use cascade drop user statement.

DROP   USER   john   CASCADE;

There are few things which you should know

  1. If the user’s schema contains tables, then Oracle Database drops the tables and automatically drops any referential integrity constraints on tables in other schemas that refer to primary and unique keys on these tables.
  2. If this clause results in tables being dropped, then the database also drops all domain indexes created on columns of those tables and invokes appropriate drop routines.
  3. Oracle Database invalidates, but does not drop, the following objects in other schemas:
  • Views or synonyms for objects in the dropped user’s schema
  • Stored procedures, functions, or packages that query objects in the dropped user’s schema
  • Oracle Database does not drop materialized views in other schemas that are based on tables in the dropped user’s schema. However, because the base tables no longer exist, the materialized views in the other schemas can no longer be refreshed.
  • Oracle Database drops all triggers in the user’s schema.
  • Oracle Database does not drop roles created by the user.

DROP USER is a DDL statement. DDL statement does not support ROLLBACK command which means once you have dropped the user you cannot get it back using ROLLBACK command.

How to drop a user when it is connected to the database

Dropping a user is a fairly simple task when user is not connected to the database, but what if user is connected to your database. Then what will you do? In that case dropping a user can be quite tricky. Here are the steps for dropping a user when it is connected to the database.

Before that let’s assume that we have a user by the name of Superman that is connected to the database and we want to drop it.

Step1: Make sure you are connected to your database using sys user with sysdba privileges. 

Step2: Now you have to put your database into the Restricted Mode. There are two ways of doing so:

Shut-down your database completely and then start it with “startup restrict” option. I don’t think this is a wise option as you will probably not want to disturb all the database users just for dropping a single user.

SQL>SHUTDOWN

C:\> STARTUP RESTRICT

Using ALTER SYSTEM command. I will recommend you to use this option. 

ALTER SYSTEM enable restricted session;

By default our database was in open startup mode and now we just changed the mode from open to restricted. When the database is started in a restricted mode, the users that are already connected can continue their work without any interruption unless they are somehow disconnected. Once any of them are disconnected they have to have a restricted session privilege which is a special system privilege to connect with the database again. Furthermore no new user can connect with a database. 

Step3:: Now we have to get the session identifier and the serial number of the user which we want to drop.

For that we will use v$session view. Such as 

SELECT   sid,  serial#   FROM   v$session   WHERE   username =’SUPERMAN’;

This query is fairly simple. We are retrieving sid which is session identifier not to be confused with the database SID which stands for system identifier. We are also retrieving the serial number from v$session view as well as we have our WHERE clause where we have specified the username. Make sure username will be in all caps and enclosed in single quotes. 

Step4: In this step we will disconnect the user superman from the database and since we are in restricted mode then this user will not be able to reconnect to the database. That’s the purpose of the restricted mode.

Using the SID and serial number which we obtained in the previous step we will now kill the session or disconnect the user.

For that just write the query

ALTER   SYSTEM   KILL   SESSION   ‘ sid, serial# ’;

*Here SID and SERIAL# are the numbers obtained from the last step.

Now this user is disconnected from the database. And you are free to drop it. 

Step5: Drop the user. 

Using drop user command you can drop this user as we did at the starting of this tutorial. If this user has objects then use drop user cascade command otherwise simple drop user command will be sufficient.

In my case I will use cascade dropping

DROP   USER   superman   CASCADE;

Step6: Now user has been dropped but our database is still into the restricted mode. We have to bring our database back into the normal mode. 

In step 2 we enabled the restricted session now we will disable it. For that we will again write an alter system query as

ALTER   SYSTEM   disable   restricted   session;

Hope you enjoyed this tutorial. Please Like and Share. Thanks & have a great day!