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

Question

So, I accidentally deleted some DBF files (only those related to my table spaces), fortunately, I just started loading data, so I did not lose anything, except that now it is impossible to recreate table spaces.

If I run:

select name from v$datafile; 

The results include the DBF files that I deleted.

I tried to run a command that I found on the Internet to delete DBF files that Oracle considers relevant:

 alter database datafile '<A_DBF_file_that_no_longer_exists>' offline drop; 

And the result:

 alter database datafile succeeded 

However, the remote data file is still returned when I run the select statement. When I try to just create new table spaces, I get an 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

Also delete the affected tablespace. Parsing a data file will not automatically drop the table space.

 DROP TABLESPACE mytablespace INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; 

Answer 2

Try

 DROP TABLESPACE <tablespace name> INCLUDING CONTENTS; 

How to delete a data file from a tablespace can be interesting for more information:

NOTE. The ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP not intended to delete a data file. What the command really means is that you drop the data file with the intention of dropping the tablespace.