Re: Is is safe to drop an empty tablespace?

My point was to try and identify anyone how MIGHT be using the tablespace ( i.e. creating and dropping tables) etc.

BTW: Dropping a tablespace does nothing for the quota. create tablespace quota_test datafile
'/oracle/data01/db330d1/quota_test_01.dbf' size 8M;

Tablespace created.

  1* create user quota_test identified by quota_test quota unlimited on quota_test
SQL> / User created.

SQL> select * from dba_ts_quotas where tablespace_name = 'QUOTA_TEST';

TABLESPACE_NAME                USERNAME                             BYTES
------------------------------ ------------------------------ -----------
  MAX_BYTES      BLOCKS  MAX_BLOCKS DRO
----------- ----------- ----------- ---
QUOTA_TEST                     QUOTA_TEST                               0
         -1           0          -1 NO

SQL> drop tablespace quota_test;

Tablespace dropped.

SQL> select * from dba_ts_quotas where tablespace_name = 'QUOTA_TEST';

TABLESPACE_NAME                USERNAME                             BYTES
------------------------------ ------------------------------ -----------
  MAX_BYTES      BLOCKS  MAX_BLOCKS DRO
----------- ----------- ----------- ---
QUOTA_TEST                     QUOTA_TEST                               0
         -1           0          -1 YES

On 3/27/08, Bobak, Mark <Mark.Bobak_at_proquest.com> wrote:
>
> 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 <mark.bobak_at_il.proquest.com>
> www.proquest.com
> 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* <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