Drop datafile from tablespace

[Former Member]

I'm new to oracle.

After I extended a tablespace I understood that it was too large.

I've been reading about how to drop a tablespace, but I don't want to drop the entire tablespace. I just want to drop the last datafile of this tablespace.

I know how to drop tablespaces in sapdba and brtools, but I don't know how I can drop one specific datafile (number 29) of the tablespace PSAPBTABD?

Please Help med.

Best regards

Harald V

[Former Member]

Hello Baran.

Unfortunatly I have oracle 9.2.

It is Windows.

What abaout stopping sap, stopping oracle and manually delete the data-file?

Best Regards

Harald V

Message was edited by:

Harald Vedvik

[Former Member]

Hello Harald,

do you have the metalink account?

see please this oracle note:

Note: <b><u>111316.1</u></b>: <i>How to 'DROP' a Datafile from a Tablespace</i>

Please read this note very <i><b><u>carefully</u></b></i> and <b><i><u>back up your database</u></i></b> before performing any action. In Oracle 9i Database it is not very easy to drop a datafile.

Best regards.

[Former Member]

you should <b><u>never</u></b> drop a datafile manually.

[fidel_vales]

Hi,

Unfortunatelly, you "cannot" drop a datafile like that.

If you do it, then ORacle will recognize that the datafile is missing and your problems will start.

In Oraclw < 10g three is no "drop datafile" option.

You cannot drop a datafile, do not try to do it, you will end "messing up" the DB.

Leave the file. You will use it in the future.

If you want to "recover" space, then change the datafile size.

The metalink Note:111316.1 clearly indicates:

Once you make a datafile part of a tablespace, the datafile CANNOT be removed, 
although we can use some workarounds.

What the note explains is to "move" the data to a "new" tablespace.