Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Dropping a 700 GB Tablespace in a Test Database Efficiently ?

RE: Dropping a 700 GB Tablespace in a Test Database Efficiently ?

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 23 Apr 2007 20:37:53 -0400
Message-ID: <00a901c78608$cc877c00$1100a8c0@rsiz.com>


Take the tablespace offline first. That excludes any work that may be attempted to recover free space along the way. Whether Oracle has optimized out this work in later releases I have not tested, but if the tablespace is offline for the drop it precludes the attempt of doing the work.  

This is why in old tangled up dictionary managed tablespaces copying out what you wanted to keep and offline dropping the rest was often faster than the pecimal freespace reclamation.  

If it is offline, that also precludes someone's query from holding up the drop.  

Regards,  

mwf  

PS: time and redo size testing the various methods on your exact release might be useful if this is something you'll be doing more than a few times. Otherwise, hmm, just do it.  


From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of VIVEK_SHARMA
Sent: Monday, April 23, 2007 4:05 PM
To: oracle-l_at_freelists.org
Subject: Dropping a 700 GB Tablespace in a Test Database Efficiently ?  

Folks  

An internal Test Database has 1 of it's Tablespace's of 700 GB which in turn Contains only 1 Table. What is the ideal Approach to DROP the same?  

Personally I think the following approach is most efficient:-

SQL> DROP TABLESPACE <Name> INCLUDING CONTENTS AND DATAFILES;  

OTHER possible Approaches:-  

SQL> TRUNCATE TABLE <Name>;

SQL> DROP TABLE <Name> PURGE; followed by the Command SQL> DROP TABLESPACE <Name> INCLUDING CONTENTS AND DATAFILES;  

Database Oracle Version 10gR2  

Seek Advice folks  

Thanks indeed      

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Apr 23 2007 - 19:37:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US