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 Harald,

see please this thread:

also the section 43. <i>How can I delete a data file that was created by mistake</i>

in the SAP Note<i><b> 592393 - FAQ: Oracle</b></i>

if you have Oracle 9i databases, it is not easy to drop a datafile, but in Oracle 10g

you can drop a datafile like that:

<b><i>ALTER TABLESPACE</i></b> <<u><i>Tablespace Name</i></u>> <b><i>DROP DATAFILE</i></b> '<<u>PATH und NAME of Datafile</u>>';

for example, you have a tablespace called PSAPC11 and you want to drop a datafile called dat.data15 of this tablespace, you can drop it like that:

e.g. <b><i>alter tablespace</i></b> <u>PSAPC11</u> <b><i>drop datafile</i></b> <u>'/oracle/C11/psapc11/dat.data15'</u>

Best regards

Baran

[Former Member]

<u><i><b>Attention:</b></i></u>

<u><b>only</b></u> if you have Oracle <b><i><u>10g</u></i></b> Databases, you can drop a datafile like that:

ALTER TABLESPACE <Tablespace Name> DROP DATAFILE '<PATH TO und THE NAME of Datafile>';