RE: Is is safe to drop an empty tablespace?

Mark,

Thank you.

To my knowledge, no process is specifically creating tables in this tablespace. And no user has this tablespace identified as a default. So I think I am covered here. I don't intend flashback to work and we don't have clusters in this database

Gene Gurevich

                                                                           
             "Mark W. Farnham"                                             
             <mwf_at_rsiz.com>                                                
                                                                        To 
             03/27/2008 02:33          <genegurevich_at_discover.com>,        
             PM                        <oracle-l_at_freelists.org>            
                                                                        cc 
                                                                           
                                                                   Subject 
                                       RE: Is is safe to drop an empty     
                                       tablespace?                         
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           

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.

Regards,

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of genegurevich_at_discover.com
Sent: Thursday, March 27, 2008 9:48 AM
To: oracle-l_at_freelists.org
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

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








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