USER statement to remove a database user and optionally remove the user's objects.
- CREATE USER for information on creating a user
- ALTER USER for information on modifying the definition of a user
You must have the
USER system privilege.
Text description of drop_user
Specify the user to be dropped. Oracle does not drop users whose schemas contain objects unless you specify
CASCADE or unless you first explicitly drop the user's objects.
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.
- If the user's schema contains tables, then Oracle 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.
- If this clause results in tables being dropped, then Oracle also drops all domain indexes created on columns of those tables and invokes appropriate drop routines.
- Oracle invalidates, but does not drop, the following objects in other schemas: views or synonyms for objects in the dropped user's schema; and stored procedures, functions, or packages that query objects in the dropped user's schema.
- Oracle 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 drops all triggers in the user's schema.
- Oracle does not drop roles created by the user.
Oracle also drops with
FORCE all types owned by the user. See the FORCE keyword of DROP TYPE.
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;