Re: Drop user cascade question!

When you issue a "DROP USER xx CASCADE;" command, it not only drops the user, but it also drops all of the objects that the user owns. When you drop the user's tables, it also drop's any indexes for those tables. Issuing the drop user cascade command now has less objects to drop so it therefore takes a shorter amount of time to process.

HTH,
Brian

"Wayne L. Phares" wrote:
>
> 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 Thu Oct 21 1999 - 07:54:07 CDT