Dropping a User in Oracle

From time to time, it becomes necessary to remove a database user (schema). Common reasons include offboarding an employee, decommissioning an application schema, or cleaning up test accounts.

In Oracle (including Oracle 23ai), the statement used to remove a user is DROP USER. Whether the drop succeeds depends on whether that user owns objects (tables, views, procedures, sequences, and so on).

DROP USER username [CASCADE];

• Without CASCADE: Oracle will only drop the user if they do not own any schema objects. This protects you from accidentally deleting a schema that contains data.
• With CASCADE: Oracle drops the user and also drops all objects owned by that user. Use this only when you are certain you want to remove the schema and its contents.

If the user owns no objects, you can drop them directly:

SQL> DROP USER COIN_ADMIN;
User dropped.

If the user owns schema objects, Oracle prevents a plain drop and requires you to be explicit. The error varies by version and context, but the meaning is consistent: the user owns objects, so you must specify CASCADE to delete the schema and its data.

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

To intentionally remove the user and everything they own:

SQL> DROP USER COIN_ADMIN CASCADE;
User dropped.

Operational note: dropping a schema can be a high-impact change. In production, confirm you have a current backup and that the schema is not required by any application, job, or integration.

If you are unsure why a user cannot be dropped (or you want to review impact before using CASCADE), query the data dictionary to see what the user owns. A practical starting point is DBA_OBJECTS (or ALL_OBJECTS if you do not have DBA privileges).

List object types owned by a user

SELECT object_type, COUNT(*) AS object_count
FROM   dba_objects
WHERE  owner = 'COIN_ADMIN'
GROUP  BY object_type
ORDER  BY object_type;

List object names and types owned by a user

SELECT object_name, object_type
FROM   dba_objects
WHERE  owner = 'COIN_ADMIN'
ORDER  BY object_type, object_name;

This gives you a straightforward inventory of what will be removed if you execute DROP USER COIN_ADMIN CASCADE.

Click the quiz link below to check your understanding of user management.

User Management - Quiz

[1]DBA_OBJECTS view: A data dictionary view that lists objects in the database (name, type, owner, status, and more). It is commonly used to inventory schema objects and assess the impact of schema-level changes.