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

dbf, oracle, oracle-xe, sql

Solution

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

DROP TABLESPACE mytablespace 
   INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Problem

So I accidentally deleted some DBF files (only ones specific 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 ```

Original source