Oracle / PLSQL: DROP USER statement

This Oracle tutorial explains how to use the Oracle DROP USER statement with syntax and examples.

Description

The DROP USER statement is used to remove a user from the Oracle database and remove all objects owned by that user.

Syntax

The syntax for the DROP USER statement in Oracle/PLSQL is:

DROP USER user_name [ CASCADE ];

Parameters or Arguments

user_name

The name of the user to remove from the Oracle database.

CASCADE

Optional. If user_name owns any objects (ie: tables or views in its schema), you must specify CASCADE to drop all of these objects.

Example

Let's look at a simple DROP USER statement.

If the user does not own any objects in its schema, you could execute the following DROP USER statement:

DROP USER smithj;

This would drop the user called smithj. This DROP USER statement will only run if smithj does not own any objects in its schema.

If smithj did own objects in its schema, you would need to run the following DROP USER statement instead:

DROP USER smithj CASCADE;

This DROP USER statement would remove the user smithj, drop all objects (ie: tables and views) owned by smithj, and all referential integrity constraints on smithj's objects would also be dropped.