RE: Quick way to drop all objects in a schema

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 19 Nov 2009 17:41:32 -0500
Message-ID: <E91A8A6868E345FA826D6061D23ED261_at_rsiz.com>

I'd wager somewhere between a nickel and a donut that you can additionally speed it up if you drop the constraints first, then the indexes, then the tables, and then, as you say, drop user cascade for completeness. Now while I have not run a speed test since before we had local extent management and the I doubt the difference is still as much it was with dictionary management. It probably only matters with local extent management when you have huge numbers of tables and indexes. A big part of the old advantage (meaning dictionary managed) was if you could take the tablespaces off line for the drop, avoiding the "pecimal" (my made up antonym for optimal) algorithm to juggle putting bits back on fet$ when others could be grabbing bits from fet$ for the same tablespace. But it also relieves Oracle of a lot of recursive sql elbow grease to figure out what order to drop things in the cascade. If you whack the constraints, then the indexes, and then the tables, that leaves a lot less for Oracle to figure out. I wouldn't be surprised if it about cut that in half again. (but that wasn't a guess Alex - I just said I wouldn't be surprised.) If you try it, please let us know whether I'm right.

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of steve montgomerie
Sent: Thursday, November 19, 2009 5:25 PM To: JDunn_at_sefas.com
Cc: oracle-l_at_freelists.org
Subject: Re: Quick way to drop all objects in a schema

We do a little of both where we drop tables and views through a cursor and then drop user cascade. I've found this to be about twice as fast as a simple drop user.

It's peoplesoft so we have about 23,000 tables, 25,000 indexes. Takes about 45 mins for us.

Steve

On Thu, Nov 19, 2009 at 4:25 AM, John Dunn <JDunn_at_sefas.com> wrote:
> Is there a quick way to drop all objects in a schema without dropping the
> user itself?
>
> This is with  Oracle 10.
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l

Received on Thu Nov 19 2009 - 16:41:32 CST