RE: Is is safe to drop an empty tablespace?

From: Bobak, Mark <Mark.Bobak_at_proquest.com>
Date: Thu, 27 Mar 2008 10:08:56 -0400
Message-ID: <667C10D184B2674A82068E06A78382B51DC38A06@AAPQMAILBX01V.proque.st>


Having quotas on a tablespace won't prevent him from dropping it. Those quotas will just go away.

If you do:
Select segment_type,segment_name from dba_segments where tablespace_name = 'TS_YOU_WANT_TO_DROP' and segment_type !='TEMPORARY'; And you get zero rows, it should be safe to drop.

To be certain,do:
Drop tablespace tablespace_you_want_to_drop; Without the 'including contents' clause, and if there's anything left in it, Oracle won't drop it.

-Mark

--

Mark J. Bobak
Senior Database Administrator, System & Product Technologies ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346 Ann Arbor MI 48106-1346
+1.734.997.4059 or +1.800.521.0600 x 4059 mark.bobak_at_proquest.com<mailto:mark.bobak_at_il.proquest.com> www.proquest.com<http://www.proquest.com> www.csa.com<http://www.csa.com>

ProQuest...Start here.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bradd Piontek Sent: Thursday, March 27, 2008 9:58 AM
To: genegurevich_at_discover.com
Cc: oracle-l_at_freelists.org
Subject: Re: Is is safe to drop an empty tablespace?

You may want to verify that no one has quotas on this tablespace. select * from dba_ts_quotas where tablespace_name = 'BLAH';

Keep in mind that schema with UNLIMITED TABLESPACE (DBA Role, used to be resource in <10g) could also be using the tablespace. On 3/27/08, genegurevich_at_discover.com<mailto:genegurevich_at_discover.com> <genegurevich_at_discover.com<mailto:genegurevich_at_discover.com>> wrote: Hi Everybody:

I am planning to drop an empty tablespace. I have confirmed that no user has it defined as a default
(select distinct default_tablespace from dba_users), and no table/index has it defined as default
(select distinct def_tablespace_name from dba_part_tables/indexes). IS there any other place
I should check to confirm that noone has this tablespace defined as default for any purposes?

thank you

Gene Gurevich

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Mar 27 2008 - 09:08:56 CDT

Original text of this message