Skip Headers
Oracle® Database SQL Language Reference
11g Release 2 (11.2)

Part Number E26088-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

DROP USER

Purpose

Use the DROP USER statement to remove a database user and optionally remove the user's objects.

In an Oracle Automatic Storage Management (Oracle ASM) cluster, a user authenticated AS SYSASM can use this clause to remove a user from the password file that is local to the Oracle ASM instance of the current node.

When you drop a user, Oracle Database also purges all of that user's schema objects from the recycle bin.

Caution:

Do not attempt to drop the users SYS or SYSTEM. Doing so will corrupt your database.

See Also:

CREATE USER and ALTER USER for information on creating and modifying a user

Prerequisites

You must have the DROP USER system privilege. In an Oracle ASM cluster, you must be authenticated AS SYSASM.

Syntax

drop_user::=

Description of drop_user.gif follows
Description of the illustration drop_user.gif

Semantics

user

Specify the user to be dropped. Oracle Database does not drop users whose schemas contain objects unless you specify CASCADE or unless you first explicitly drop the user's objects.

Restriction on Dropping Users 

You cannot drop a user whose schema contains a table that uses a flashback data archive for historical tracking. You must first disable the table's use of the flashback data archive.

CASCADE

Specify CASCADE to drop all objects in the user's schema before dropping the user. You must specify this clause to drop a user whose schema contains any objects.

Caution:

Oracle Database also drops with FORCE all types owned by the user. See the FORCE keyword of DROP TYPE.

Restriction on Dropping Users 

You cannot drop a user whose schema contains a table that uses a flashback data archive. You must first disable use of the flashback data archive.

Examples

Dropping a Database User: Example If user Sidney's schema contains no objects, then you can drop sidney by issuing the statement:

DROP USER sidney; 

If Sidney's schema contains objects, then you must use the CASCADE clause to drop sidney and the objects:

DROP USER sidney CASCADE;