Dropping User in Oracle

From time to time, it becomes necessary to remove users from a database. Perhaps an employee leaves the company or an old application is being removed. In Oracle, the command to remove a user is DROP USER, and the syntax looks like this:

DROP USER username [CASCADE];

This syntax, as you can see, is very simple. The only option is the CASCADE option, which controls whether or not a user can be dropped when that user owns data. If you have a user who does not own any objects, use the command without the CASCADE option. For example:

SQL> DROP USER COIN_ADMIN;
User dropped.

Continuing with this example, if the COIN_ADMIN user had owned an object such as a stored procedure or a table, the drop would have failed, as this example illustrates:

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

This behavior of the DROP command helps prevent you from inadvertently dropping a user and losing data. If you purposely want to drop both a user and all objects owned by that user, you must explicitly indicate your intent by adding the keyword CASCADE to the command.
For example:

SQL> DROP USER coin_admin CASCADE;

User dropped.

If you find yourself unexpectedly prevented from dropping a user because the CASCADE option was not specified, you can query DBA_OBJECTS view to find out just what objects that user owns.

If you ever need to know if a user owns any objects and what they are, the DBA_OBJECTS view can provide the answer. DBA_OBJECTS returns one row for each object in the database. Query it from SQL*Plus like this:
Categorize the Main Database Objects There are various types of objects that can exist within a database, many more with the current release than with earlier versions. All objects have names, and all objects are owned by someone. The "someone" is a database user, such as Human Resources. The objects the user owns are their schema. An object's name must conform to certain rules.

This query lists the object types that happen to exist in this particular database, with a count of how many there are:

SELECT object_type, count(object_type)
FROM dba_objects
GROUP BY object_type
ORDER BY object_type;

Oracle has many types of objects in dba_objects and you can display them by querying the dba_objects view^[1].

SQL > select distinct object_type from dba_objects;

OBJECT_TYPE
-------------
CLUSTER
FUNCTION
INDEX
PACKAGE
PACKAGE BODY
PROCEDURE
SEQUENCE
SYNONYM
TABLE
TRIGGER
VIEW

SQL> COLUMN object_name FORMAT A30
SQL> SELECT object_name, object_type
  2  FROM dba_objects
  3  WHERE owner='SYSTEM'
  4  ORDER BY object_type, object_name;
OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
AQ$_QUEUES_CHECK               INDEX
AQ$_QUEUES_PRIMARY             INDEX
AQ$_QUEUE_TABLES_PRIMARY       INDEX
AQ$_SCHEDULES_CHECK            INDEX
AQ$_SCHEDULES_PRIMARY          INDEX
DEF$_CALLDEST_N2               INDEX

This query returns a list of objects owned by the SYSTEM user. The COLUMN command is not strictly necessary, but it shortens the OBJECT_NAME column so the query results do not wrap around to two lines.

Click the Quiz link below to answer a few questions about user management.
User Management - Quiz

[1]dba_objects view: The dba_objects view in Oracle is a data dictionary view that provides information about all database objects. It is an extremely useful tool for database administrators (DBAs) and developers to understand the structure and contents of their database.