Re: Fastest way to drop tablespace
Date: Fri, 04 Apr 2008 09:33:22 -0700
Message-ID: <1207326799.591077@bubbleator.drizzle.com>
suri 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
Take the tablespace offline.
Drop the tablespace including contents and datafiles.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Fri Apr 04 2008 - 11:33:22 CDT