Path: text.usenetserver.com!out01b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!news1.google.com!news.glorb.com!feeder.erje.net!newsfeed.xs4all.nl!newsfeed3.news.xs4all.nl!newsgate.cistron.nl!xs4all!transit3.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
From: "Shakespeare" <whatsin@xs4all.nl>
Newsgroups: comp.databases.oracle.server
References: <62ebccc9-1035-4f77-a2d7-868a747a0777@u36g2000prf.googlegroups.com>
Subject: Re: Fastest way to drop tablespace
Date: Fri, 4 Apr 2008 17:25:50 +0200
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Original
Lines: 32
Message-ID: <47f6487f$0$14342$e4fe514c@news.xs4all.nl>
NNTP-Posting-Host: 82.95.215.210
X-Trace: 1207322751 news.xs4all.nl 14342 [::ffff:82.95.215.210]:56417
X-Complaints-To: abuse@xs4all.nl
Xref: usenetserver.com comp.databases.oracle.server:443424
X-Received-Date: Fri, 04 Apr 2008 10:25:51 EST (text.usenetserver.com)


"suri" <suriawan.limantara@googlemail.com> schreef in bericht 
news:62ebccc9-1035-4f77-a2d7-868a747a0777@u36g2000prf.googlegroups.com...
> 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

Just curious (absolutely no criticism): how do you get 300000 tables?

Shakespeare