Dropping User bug V7.1.3

From: Ari Kaplan <akaplan_at_thymaster.interaccess.com>
Date: 1995/08/22
Message-ID: <41dll1$5br_at_nntp.interaccess.com>#1/1

I would like to respond to a previous post about a bug in dropping users in Oracle 7.1.3 (at least on UNIX). Apparently, when you issue the "DROP USER username" command, it can take up to 20 hours, running the serial# of the session serially upwards. After talking with Oracle support (and experimenting on my own), it appears that this problem occurs when the user has objects, especially packages.

The best method of dropping a user then is to drop the tables, sequences, synonyms, packages, etc. from the user. Then, dropping the user (without the CASCADE) clause, will work normally.

FYI, you can write a script to drop the user's objects. An example for tables follows:

---

SET PAUSE OFF
SET HEAD OFF
SPOOL X.SQL
SELECT 'DROP TABLE username.'||TABLE_NAME||';'

   FROM DBA_TABLES
   WHERE OWNER = 'username';
SPOOL OFF
SET ECHO ON
_at_X

---

Note: be sure to replace "username" with the proper username above.

Hope this helps.

-Ari
akaplan_at_interaccess.com Received on Tue Aug 22 1995 - 00:00:00 CEST