Re: Deleting a tablespace

In article <X5MRBFYD_at_math.fu-berlin.de> wiersma_at_gemini writes:
>In article <32on8e$e8a_at_oac4.hsc.uth.tmc.edu>, Shirley <shirley_at_odin.mda.uth.tmc.edu> writes:
>>
>>I need to drop a tablespace, but before I do it,
>>I would like to make sure that I am doing it correctly.
>>
>>Here is the list of steps:
>>1. Bring the tablespace offline
>>2. Drop all tables residing in the tablespace
>> with the cascade constraints option on
>>3. Drop all users associated with the tablespace
>>4. Drop the tablespace including contents
>>5. Alter the database to bring the datafile offline
>>6. Delete the data file
>>
>>Thank you very much for your help.
>
>
>First of all, which version of Oracle do you use? Second, info on your platform
>may be handy too.
>
>1) OK
>2) Not necessary, but some DBA's do that in Oracle 6. Use:
>drop tablespace <name> including contents;
>3) Not necessary unless these users are not allowed access anymore.
>4) We did that in step 2)
>5) No.
>6) Yes.
>
>Cheers,
>
>Fred Wiersma
>wiersma_at_tfdl.agro.nl

It is always safer to drop all the tables ,indexes etc in the tablespace and then drop the tablespace without specifying the including contents option. The tables etc in the tablespace can be queried from dba_segments to find out the names of the tables and indexes. The reason for not using the including contents option is that , by using that option ,there is a chance that a wrong tablespace maybe dropped. By ensuring the removal of the tables and indexes , the chanes of typing in a wrong tablespace is minimised ,in the sense that Oracle would automatically inform you of the fact that the tablespace is not empty. Also ,the including contents option may take a longer time. This option should be used only after ensuring that the name of the tablespace being typed is correct.

Ramesh Krishnamurthy Received on Wed Aug 17 1994 - 22:24:37 CEST