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. This article outlines the step to ensure that the user does not own any objects.
1. Logon to SQL*PLUS with DBA privileges.
2. Select the number of objects owned by the user account you wish to delete with the following statement. In this example we are trying to delete user JDOE.
SQL> select count(*) from dba_objects where owner='JDOE';
COUNT(*)
----------
0
SQL>
3. If the above SQL statement returns a number greater than 0, further investigation is required. If the above SQL statement returns 0, you can safely remove the user account with the below statement.
SQL> drop user jdoe;
User dropped.
SQL>
Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com