Oracle DROP USER and CASCADE: Delete a User and Schema

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.

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 [IF EXISTS] 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 the objects owned by that user, along with some, but not all, effects on objects in other schemas that reference them, covered in detail below.
• With IF EXISTS: the statement completes quietly rather than raising an error if the named user has already been removed, useful for cleanup scripts that may run more than once against the same environment.

One restriction worth knowing before you attempt any drop: a user's schema containing a table tracked by a flashback data archive cannot be dropped until that table's flashback data archive tracking is disabled first.

SELECT sid, serial#, status
FROM v$session
WHERE username = 'COIN_ADMIN';

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 exact error message can vary, 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'

Cause: the user owns objects that must be dropped along with the user. Action: use DROP USER username CASCADE; if you intend to drop the user and everything they own. Worth keeping distinct: privileges granted to the user are not the same thing as objects owned by the user, and only the latter triggers this error.

To intentionally remove the user and everything they own:

SQL> DROP USER COIN_ADMIN CASCADE;
User dropped.

Dropping a user also purges that user's recycle-bin objects. The SQL*Plus transcripts above are illustrative, not execution results from this lesson. 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.

CASCADE's effects on the dropped user's own schema are thorough: tables, views, indexes, sequences, triggers, and PL/SQL objects the user owns are all removed. Its effects on objects in other schemas are more specific, and in one important respect more limited, than "everything related gets cleaned up." This is worth knowing precisely before you rely on CASCADE for anything beyond a trivial, isolated schema:

• Foreign keys elsewhere are dropped outright. If a table in another schema has a foreign key referencing a primary or unique key on one of the dropped user's tables, that constraint is automatically dropped along with the table it referenced.
• Views, synonyms, and PL/SQL in other schemas are invalidated, not dropped. A view, synonym, stored procedure, function, or package in a different schema that references one of the dropped user's objects is not removed at all. It becomes invalid and stays that way until someone drops it manually or recreates whatever it depended on. This is the detail most likely to surprise a DBA: CASCADE cleans up the dropped user's own schema completely, but leaves broken references sitting in other schemas rather than clearing them away.
• Materialized views elsewhere survive, but lose their refresh source. Their base tables are gone, so any materialized view built on them in another schema cannot refresh while its base tables are absent; it is not dropped automatically.
• Roles created by the dropped user are not removed. Only the user's own schema objects and their privilege grants are affected.

If you are unsure why a user cannot be dropped, or you want to review impact before using CASCADE, two separate questions are worth asking, since one query does not answer both of them.

What does this user own?

A practical starting point is DBA_OBJECTS (use an authorized DBA for a complete review; ALL_OBJECTS only shows accessible objects), which inventories many objects CASCADE will remove from the target user's own schema:

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

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

One caution worth taking seriously: if this query comes back empty, or nearly empty, but Oracle still refuses a plain DROP USER with ORA-01922, do not assume the error is wrong. Not every schema-owned item is guaranteed to show up cleanly in a general DBA_OBJECTS scan the way an ordinary table or view does. If you hit this situation, check specialized views for the specific object types the schema might hold, such as DBA_SCHEDULER_JOBS for scheduled jobs or DBA_QUEUES for Advanced Queuing queues, rather than concluding the schema is genuinely empty just because DBA_OBJECTS looks that way.

What in other schemas depends on this user's objects?

This is a genuinely different question, and DBA_OBJECTS does not answer it. Two more targeted queries cover the two distinct risks covered above.

To find views, synonyms, and PL/SQL objects in other schemas that will be invalidated rather than dropped:

SELECT owner, name, type
FROM   dba_dependencies
WHERE  referenced_owner = 'COIN_ADMIN'
AND    owner <> 'COIN_ADMIN';

To find foreign key constraints in other schemas that will be silently dropped along with this user's tables, note this requires a different view entirely, since DBA_DEPENDENCIES does not track table-to-table foreign key relationships at all:

SELECT c.owner, c.constraint_name, c.table_name
FROM dba_constraints c
JOIN dba_constraints p
  ON p.owner = c.r_owner
 AND p.constraint_name = c.r_constraint_name
WHERE c.constraint_type = 'R'
  AND p.owner = 'COIN_ADMIN'
  AND c.owner <> 'COIN_ADMIN';

These queries provide a starting inventory of what happens when you execute DROP USER COIN_ADMIN CASCADE: what disappears entirely, what gets invalidated elsewhere, and what foreign keys elsewhere get dropped. Matching both referenced owner and constraint name avoids false matches across schemas. Also review application SQL, dynamic SQL, jobs, and integrations: dictionary dependencies do not provide a complete operational impact inventory.

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. It shows what a user owns, but not what other schemas' objects depend on that user's objects; DBA_DEPENDENCIES and DBA_CONSTRAINTS answer that second question instead.