Question Re: Drop datafile from tablespace in Technology Q&A

Drop datafile from tablespace

Former Member — Mon, 22 Oct 2007 15:36:27 GMT

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

Re: Drop datafile from tablespace

Former Member — Mon, 22 Oct 2007 17:26:52 GMT

Hello Harald,

see please this thread:

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

in the SAP Note 592393 - FAQ: Oracle

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:

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

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. alter tablespace PSAPC11 drop datafile '/oracle/C11/psapc11/dat.data15'

Best regards

Baran

Re: Drop datafile from tablespace

Former Member — Mon, 22 Oct 2007 17:32:23 GMT

Attention:

only if you have Oracle 10g Databases, you can drop a datafile like that:

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

Re: Drop datafile from tablespace

Former Member — Mon, 22 Oct 2007 17:55:04 GMT

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

Re: Drop datafile from tablespace

Former Member — Mon, 22 Oct 2007 18:18:04 GMT

Hello Harald,

do you have the metalink account?

see please this oracle note:

Note: 111316.1: How to 'DROP' a Datafile from a Tablespace

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

Best regards.

Re: Drop datafile from tablespace

Former Member — Mon, 22 Oct 2007 18:19:19 GMT

you should never drop a datafile manually.

Re: Drop datafile from tablespace

Former Member — Mon, 22 Oct 2007 18:25:42 GMT

Thanks again for your answers Baran.

I have no metalink account.

Best regards.

Harald Vedvik

Re: Drop datafile from tablespace

Former Member — Mon, 22 Oct 2007 18:32:44 GMT

Hi Harals,

Check note 758563 for metalink access for SAP customers.

Thanks

Prince Jose

Re: Drop datafile from tablespace

fidel_vales — Mon, 22 Oct 2007 18:47:52 GMT

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.

Re: Drop datafile from tablespace

Former Member — Mon, 22 Oct 2007 19:03:26 GMT

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.

Re: Drop datafile from tablespace

Former Member — Mon, 22 Oct 2007 19:08:54 GMT

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

Re: Drop datafile from tablespace

madhukara_shenoy — Mon, 22 Oct 2007 19:39:39 GMT

Hello,

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

Regards,

Madhukar