How to drop data file?

[Former Member]

Hi,

I had create tablespace PSAPSBXTEST on our sand server using brtools, I extend the tablespace & add two more data files to it.

After that I drop the tablespace but while droping the tablespace brtools is unable to delete the data files & directories.

I want to delete the data files, can anybody suggest me how can I delete the data files?

Regards,

Rajesh

[lbreddemann]

HI Rajesh,

it may be possible that (on windows) you'll have to stop the db instance first to manually delete the datafiles.

For the future you may want to use the

DROP TABLESPACE ... INCLUDING CONTENTS AND DATAFILES;

command.

Oracle will then automatically delete the files for you.

That option is also available in the BRTOOLS but you've to specify it explicitely.

Best regards,

Lars

[Former Member]

if you delete a tablespace, the datafiles of this tablespace will be also deleted.

Do you have the datafiles still on the database?

<i><b>select name from v$datafile</b>

<b><b>select TABLESPACE_NAME, FILE_NAME from dba_data_files where TABLESPACE_NAME</b>=<b><u>'tablespace_name'</u></b>>[/</b>i]

Best regards

Baran

[Former Member]

do you have an Oracle metalink account?

See please the Oracle Note:

How to 'DROP' a Datafile from a Tablespace

Doc ID: Note:111316.1

Best regards

Baran

[Former Member]

see please

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

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

It depends on which oracle version you have.

best regards

Baran