Drop user cascade question!

From: Wayne L. Phares <pharesw_at_teoco.com>
Date: Wed, 20 Oct 1999 17:06:29 -0400
Message-ID: <380E2ED5.CDD0FD37@teoco.com>

OK, here is the deal:

We have a medium size DB with two user ID's, when we want to reload a saved copy we drop both users and import.

Dropping the users has always been done like this: DROP USER XX CASCADE; This process takes upwards of TWO HOURS!

I have come up with this
blah...
blah...
select distinct 'DROP TABLE '||o.OBJECT_NAME||';' from user_objects o
where o.object_type = 'TABLE';
blah...
blah...

I do this for each 'TYPE' of (SEQUENCE, PROCEDURE, VIEW, FUNCTION, etc...)
I run it as the user I want to remove and it removes ALL objects that are owned by that user.

After this I login as the system manager and: DROP USER XX; This whole procedure takes under 5 minutes.

So, what the hell is the cascade doing and am I missing something by dropping each object type manually?

Thanks!
Wayne Received on Wed Oct 20 1999 - 16:06:29 CDT