RE: Is is safe to drop an empty tablespace?

From: Mark W. Farnham <>
Date: Thu, 27 Mar 2008 15:33:42 -0400
Message-ID: <000001c89041$7ae9dd40$>

One slight gotcha is whether any prior existing object is within the flashback guaranteed.

Presumably you intend to drop the tablespace AND whack the files to get back space.

I haven't tried that, but I think you *might* be able to make such a query fail.

Still, if you don't intend that to work for folks (which seems likely) you should be okay.

Oh - check clusters too, but folks don't use them much anymore except for the single table cluster trick(s), 'cause maintenance like the purging part of archiving and purging is a pain for clusters.

Of course you can't tell from the database alone whether any batch processes use permanent tables in that tablespace on the fly and then clean up after themselves. You would discover any such process next time it ran after you whacked the tablespace. If it is not re-runnable that might be a problem.



-----Original Message-----
From: [] On Behalf Of
Sent: Thursday, March 27, 2008 9:48 AM
Subject: Is is safe to drop an empty tablespace?

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


Received on Thu Mar 27 2008 - 14:33:42 CDT

Original text of this message