Lesson 13	Dropping a User
Objective	Delete users and their data from your Database

Dropping User in Oracle

From time to time, it becomes necessary to remove users from a database. Perhaps an employee leaves the company or an old application is being removed. In Oracle, the command to remove a user is DROP USER, and the syntax looks like this:

DROP USER username [CASCADE];

This syntax, as you can see, is very simple. The only option is the CASCADE option, which controls whether or not a user can be dropped when that user owns data. If you have a user who does not own any objects, use the command without the CASCADE option. For example:

SQL> DROP USER COIN_ADMIN;
User dropped.

Continuing with this example, if the COIN_ADMIN user had owned an object such as a stored procedure or a table, the drop would have failed, as this example illustrates:

SQL> DROP USER coin_admin;
DROP USER coin_admin
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 
'COIN_ADMIN'

DROP Command

This behavior of the DROP command helps prevent you from inadvertently dropping a user and losing data.
If you purposely want to drop both a user and all objects owned by that user, you must explicitly indicate your intent by adding the keyword CASCADE to the command.
For example:

SQL> DROP USER coin_admin CASCADE;

User dropped.

If you find yourself unexpectedly prevented from dropping a user because the CASCADE option was not specified, you can query DBA_OBJECTS view to find out just what objects that user owns.

User Management - Quiz

Click the Quiz link below to answer a few questions about user management.
User Management - Quiz