Why isn't Oracle DROP USER CASCADE removing all remants of a user from disk?

Question

I need to eliminate all remnants of specific users from an Oracle 11g R2 database. This means not only deleting the users one by one, but also deleting all objects associaated with that user, in addition to all physical remnants on the disk, such as .dbf files.

I read several articles suggesting syntax for this, and settled on the following series of two lines for each user:

DROP USER <username> CASCADE;
DROP TABLESPACE <username> INCLUDING CONTENTS AND DATAFILES;  

I then typed SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS; and confirmed that the USER with the specific username was not included in the results.

But I also have the folder containing the .DBF files open, and I notice that the .DBF files are not deleted even though the Oracle SQL Developer interface tells me that both of the two above commands succeeded.

What specific syntax or other actions do I need to take in order to delete EVERY remnant of the user and its associated schema, etc. from an Oracle 11g R2 database?

---

ONGOING RESEARCH:

---

After reading @EliasGarcia's approach, I tried his first command select tablespace_name from dba_data_files where file_name = 'file_including_path' for the same username that was used in the preceding commands in the OP. But this query did not produce any results because the table space was deleted by the two commands shown above in my OP.

Given that I have to delete the user and all objects related to the user also, can someone please show how to combine the OP approach with @EliasGarcia's approach? For example, the OP is asking for something like DROP USER username CASCADE INCLUDING CONTENTS AND DATAFILES;

I hesitate to simply delete the .dbf files after running the above commands.

Answer 1

Datafiles are allocated to tablespaces, not users. Dropping the users will remove the tables etc. from the tablespace, but the underlying storage is not affected.

Don't delete the dbf files from the filesystem directly, you'll get your database into a mess. To remove them, find which tablespace the files belong to using the following statement:

select tablespace_name
from   dba_data_files
where  file_name = <file name with full path>;

You can then remove the file(s) by dropping the tablespace:

drop tablespace <tablespace_name> including contents and datafiles;

Before doing this you should verify that there aren't any objects still allocated to the tablespace for other users however. You can find this by running:

select * from dba_segments
where  tablespace_name = <tablespace to drop>;

If this returns anything, move the objects to another tablespace if you want to keep them before dropping the tablespace.