RE: Dropping a 700 GB Tablespace in a Test Database Efficiently ?
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <VIVEK_SHARMA@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 23 Apr 2007 20:37:53 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of VIVEK_SHARMA
Sent: Monday, April 23, 2007 4:05 PM
To: oracle-l@xxxxxxxxxxxxx
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***
Other related posts:
