Path: text.usenetserver.com!out03b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!u36g2000prf.googlegroups.com!not-for-mail From: suri Newsgroups: comp.databases.oracle.server Subject: Fastest way to drop tablespace Date: Fri, 4 Apr 2008 05:44:29 -0700 (PDT) Organization: http://groups.google.com Lines: 23 Message-ID: <62ebccc9-1035-4f77-a2d7-868a747a0777@u36g2000prf.googlegroups.com> NNTP-Posting-Host: 192.100.130.7 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Trace: posting.google.com 1207313069 22899 127.0.0.1 (4 Apr 2008 12:44:29 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Fri, 4 Apr 2008 12:44:29 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: u36g2000prf.googlegroups.com; posting-host=192.100.130.7; posting-account=uRRt4QoAAAC4-zirZ1lgb3ZX551rJCNR User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.12) Gecko/20080201 Firefox/2.0.0.12,gzip(gfe),gzip(gfe) Xref: usenetserver.com comp.databases.oracle.server:443421 X-Received-Date: Fri, 04 Apr 2008 07:44:29 EST (text.usenetserver.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