RE: Quick way to drop all objects in a schema

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <stmontgo@xxxxxxxxx>, <JDunn@xxxxxxxxx>
  • Date: Thu, 19 Nov 2009 17:41:32 -0500

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of steve montgomerie
Sent: Thursday, November 19, 2009 5:25 PM
To: JDunn@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx> wrote:
> Is there a quick way to drop all objects in a schema without dropping the
> user itself?
>
> This is with  Oracle 10.
>
>
>
>
--
//www.freelists.org/webpage/oracle-l




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


Other related posts: