Re: Fastest way to drop tablespace

On Apr 4, 5:44 am, suri <suriawan.limant..._at_googlemail.com> wrote:
> Hi,
>
> we have very big tablespace 300GB containing 300000 tables and we want
> to drop them. We don' t need
> the contents, so no need to recover them.
>
> What is the fastest way and less load for the DB to drop it? We have
> Oracle 9.2.0.8 on HP-UX 11.11
>
> We did 2 small tests on lab. The first one is the usual "drop
> tablespace including contents" and the 2nd one
> we put the DB files to offline first and then drop the tablespace. We
> traced both methods. The 2nd method is
> much faster. The different to the first method is that drops the
> tablespace without take the db files offline has
> lot of "enqueue" wait events.
>
> We are wondering what is Oracle doing internally in both methods? and
> is it so that dropping a tablespace by
> first taking the DB file offline is always faster.
>
> Thanks,
> Suri

Are you DMT or LMT? If DMT, probably space management enqueue trying to keep the various system table objects from stepping on each other in some logical manner. Doesn't have to be so logical if offline.

See http://www.nocoug.org/download/2006-08/unit5_enqueues.ppt (among other things you can find googling) for how to tell, or see what a statspack has to say.

jg

--
@home.com is bogus.
"The reason why they want Windows is, they think of Windows as being a
real PC." - Michael Dix, a general manager for Microsoft's Windows
group.