Drop tablespace in Oracle?

Question

I'm a newbie in handling databases and need a little help..

Does anyone here knows what happens when an Oracle tablespace is dropped? The tables will be removed, but will the database space for this tablespace be removed as well?

Thanks.

Answer 1

If you drop the tablespace, anything in the tablespace will be lost. I don't know what platform you are using but in UNIX, dropping the tablespace does not necessarily delete the database files that were used to store the tablespace.

Scenario:
We have a 3GB database MYDATA that contains 50 or so tables, and these are located in DATATBS tablespace. The tablespace is actually made up of four 2GB data files:
/mydata/datafile1.dbf
/mydata/datafile2.dbf
/mydata/datafile3.dbf
/mydata/datafile4.dbf

If we tell it to DROP DATABASE, it will just get rid of the 3GB of data and free that up in the tablespace, but any other database within DATATBS is unaffected.

On the other hand if we say DROP TABLESPACE INCLUDING CONTENTS you are getting rid of any databases in that tablespace and the tablespace itself, but the empty database files are left on the system (reserving the space).

I think some versions of Oracle allow you to DROP TABLESPACE INCLUDING CONTENTS AND DATA FILES. In that case, it should also delete the four database files and free up the disk space.

If you want to study more info on Oracle, check out the Tahiti website that Oracle has which contains all the documentation you should ever need.