Dropping a User in Oracle AI Database 26ai

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.

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.

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 can never refresh again. Their base tables are gone, so any materialized view built on them in another schema becomes permanently stale without being dropped itself.
• 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 (or ALL_OBJECTS if you do not have DBA privileges), which inventories everything 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';

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
WHERE  c.constraint_type = 'R'
AND    c.r_constraint_name IN (
   SELECT constraint_name
   FROM   dba_constraints
   WHERE  owner = 'COIN_ADMIN'
   AND    constraint_type IN ('P', 'U')
);

Together, these three queries give a genuinely complete 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 without warning.

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.