Re: Drop user cascade question!

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Thu, 21 Oct 1999 19:21:19 +0800
Message-ID: <380EF72F.33FB@yahoo.com>

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

Its one statement - which means it needs to be able to be rolled back if the database crashes (or you hit Ctrl-C etc)...

Thats a lot of overhead...by dropping the objects for that user first, you minimise this.

--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Thu Oct 21 1999 - 06:21:19 CDT