WHAT HAPPEN WHEN WE DROP TABLESPACE

[Rizwan_tau]

hi,

If we drop tablespace, then the data or datafile in the tablespace remains there, or dropped too.

I mean if we drop tablespace, will the datafiles in the tablespace are also dropped.

[oravijay]

Hi,

When you drop a tablespace, Oracle removes the datafiles from it's data dictionary views and updates the controlfiles. So the files associated with the dropped tablespace is not required.

Prior to Oracle9i we have to manually delete the files from the OS. From Oracle9i you have an option of creating Oracle Managed Files where in Oracle takes care of creating a file, maintaining a file and deleting a file(when the tablespace is dropped).

HTH.

Thanks.
Vijay.

[Rizwan_tau]

U mean the data in that tablespace is also removed.

Thanks u very much, if u have further informaton please let me know.

[yls177]

i am on sap. and drop a tablespace through sapdba.

SAPDBA: ORACLE has obviously not released the filesystem space of 17811928 K(Before: 5453816 K, Now: 5453828 K, 100 K reserve for log info, ...).This sluggish behavior is well known. It will take a few minutes or a DB reboot to release all the space!

and i checked my vg, lv... the space did not increase.

i must restart my oracle?

[yanban]

Originally posted by oravijay
Oracle Managed Files where in Oracle takes care of creating a file, maintaining a file and deleting a file(when the tablespace is dropped).

Thought even if they are not OMF, Oracle can still delete the file for you.
You would use "drop tablespace XXX including contents and datafiles".

[davey23uk]

did you delete the physical file?

[yls177]

Originally posted by yls177
i am on sap. and drop a tablespace through sapdba.

SAPDBA: ORACLE has obviously not released the filesystem space of 17811928 K(Before: 5453816 K, Now: 5453828 K, 100 K reserve for log info, ...).This sluggish behavior is well known. It will take a few minutes or a DB reboot to release all the space!

and i checked my vg, lv... the space did not increase.

i must restart my oracle?

sapdba will delete the files at the os level

[pando]

AFIAK HP-UX takes a while to free the space (if you are not using HP-UX then ignore this)

[yls177]

i have seen RTFM and have gather what it means..

but this "AFIAK".. i really dont know.. can tell me?

[yls177]

i am on aix. so upon dropping the tablespace, and deleting the physical space, i should be able to see the space increased?