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

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      

• CAUTION - Disclaimer ***************** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS******** End of Disclaimer ********INFOSYS***

--
http://www.freelists.org/webpage/oracle-l