DBF file exist while user has been dropped?

Question

I am new to Oracle. I have dropped some users from my database using:

DROP USER username cascade;

The issue is that the .dbf file still exists in the $ORACLE_HOME/dbs/oradata/ directory, which is creating disk space issues.

Can I remove the unused file directly or by some other processes? Also the size of the .trc file has increased to 16 GB. How can I clean that up?

I can delete these file directly but not sure that it may cause any start up or configuration issue, which I don't want.

Simply my concern is to get rid of disk space issue from the above unwanted files.

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.