[Oracle] Remove tablespace with missing DBF file

Well, nobody's perfect. But if a software is good we can afford not to be flawless.

Say instead of dropping a tablespace the proper way from your Oracle DB, you deleted its DBF file instead; how can you make Oracle forget about this and let you create a new one with the same name and location?

Luckily, you can still salvage the situation by issuing some commands while connected as sys:

SELECT * FROM sys.dba_data_files;

Now find your tablespace and copy the value from the FILE_NAME column, then delete the file association:

ALTER DATABASE DATAFILE 'file_name_we_got_before' OFFLINE DROP;

Finally, drop the tablespace itself:

DROP TABLESPACE your_tablespace INCLUDING CONTENTS;

And you're back in business