In an oracle database every user has the ability (may not have the privilege) to own objects, this collection of objects is referred to as a schema. A typical user of a database application will not have any objects within their schema. To safely remove an Oracle user you must ensure that they do not own any objects which may be referenced by other users. The cascade option of the drop command, allows the administrator to by-pass the deletion of objects owned by the user. NOTE: It is very important to verify that you do not need any objects owned by a user before dropping the account with the cascade option. This article outlines the step to list all objects owned by a user before dropping the account with the cascade option..
1. Logon to SQL*PLUS with DBA privileges.
2. Select the object name and type owned by the user account you wish to delete with the following statement. In this example we are trying to delete user JDOE.
SQL> column object_name format a30
SQL> select object_name, object_type from dba_objects where owner='JDOE';
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
TEMP TABLE
SQL>
3. If the above SQL statement returns a number greater than 0, you should validate that the objects are not used by any other user. If the above SQL statement returns 0, you can remove the user account without the cascade option as shown below.
SQL> drop user jdoe;
User dropped.
SQL>
4. Once you are sure that the objects are not being used by any other user, use the following SQL to remove the account and all associated objects.
SQL> drop user jdoe cascade;
User dropped.
SQL>
NOTE: The associated table TEMP was removed before the actual account was removed.
Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com