Deleted some Oracle DBF files by mistake - how can I tell Oracle XE to forget about them?

Question

So I accidently deleted some DBF files (only ones specfic to my tablespaces), fortunately I was only just about to start loading data in so have lost nothing, except now can't re-create the tablespaces.

If I run:

select name from v$datafile;

The results include the DBF files that I deleted.

I attempted to run a command I found on the internet, to delete the DBF files that Oracle thinks are relevant:

alter database datafile '<A_DBF_file_that_no_longer_exists>' offline drop;

And the result is:

alter database datafile succeeded

However the datafile deleted is still returned when I run the select statement. When I try to just create new tablespaces, I get the error:

SQL Error: ORA-01543: tablespace 'my_tablespace_name' already exists           
01543. 00000 -  "tablespace '%s' already exists"
*Cause:    Tried to create a tablespace which already exists
*Action:   Use a different name for the new tablespace

Answer 1

Try

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;

How to drop a datafile from a tablespace could be interesting for more information:

NOTE: The ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP command, is not meant to allow you to remove a datafile. What the command really means is that you are offlining the datafile with the intention of dropping the tablespace.

Answer 2

Drop the affected tablespace, too. Droping the datafile will not automagically drop the tablespace.

DROP TABLESPACE mytablespace 
   INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;