Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Nov 20 '12 at 7:45

This question came from our site for professional and enthusiast programmers.

add comment

1 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.