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 · Accepted solution]

Harald,

you have a few possibilities:

1. decrease the size of your datafile to a small value:

brspace -c -u / -f dfalter

2. reorg fully your tablesapce

check Note 646681 - Reorganizing tables with BRSPACE

as you can see, you cannot drop a datafile easily in Oracle 9.

[madhukara_shenoy]

Hello,

You are right! If you want the space on disk, you can resize it the smaller size.

Regards,

Madhukar

[Former Member]

Hello and thanks for all the respons.

I've got metalink access and have been reading oracle note 111316.1 and note 1029252.6 How to resize a datafile.

I now understand that my best option is to leave the data-file.

Maybe resize it.

Best regards Harald Vedvik

[Former Member]

Thanks again for your answers Baran.

I have no metalink account.

Best regards.

Harald Vedvik

[Former Member]

Hi Harals,

Check note 758563 for metalink access for SAP customers.

Thanks

Prince Jose

[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.

[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>';