How to delete .dbf file in oracle?

Question

I am trying to recreate a tablespace in Oracle and get the following error message:

 "created file already exists" 

I am running the following script:

create smallfile tablespace dev_01 datafile 'C:\Oradata\db1\devdata\dev_01 .dbf' size 500M 

Any ideas? I have tried killing any sessions that were using the file.

Answer 1

Oracle in Windows does not drop the datafile, despite the synax in the INCLUDING clause. You can manually delete it using Windows, or you can simply use:

CREATE SMALLFILE TABLESPACE dev_01 DATAFILE 'C:\Oradata\db1\devdata\dev_01.dbf' SIZE 500M REUSE; 

This will reuse the existing file if it exists.

Answer 2

I've tried this command which complains that the tablespace does not exist:

DROP TABLESPACE dev_01 INCLUDING CONTENTS AND DATAFILES;