DBF file exist while user has been dropped

oracle

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.

Best Answer

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.

Related Solutions

Windows – How do we free up disk space from allocated namespace? / ORA-03297

You are receiving an Oracle ORA-03297 error because the HWM (High Water Mark) of a table is beyond the size you tried to shrink a datafile to.

First try and "shrink space" for each affected table:

alter table fragmentedtable enable row movement;

alter table fragmentedtable shrink space;

Next, check to see how much space can be freed from each of the existing tablespace files without moving the current database objects.

The following (taken from Ask Tom & assuming a block size of 8k) tells you how much space you can currently free from each datafile, in megabytes:

select file_name,
       ceil( (nvl(hwm,1)*8192)/1024/1024 ) SHRINK_TO,
       ceil( blocks*8192/1024/1024) CURRENT_SIZE,
       ceil( blocks*8192/1024/1024) -
       ceil( (nvl(hwm,1)*8192)/1024/1024 ) SAVINGS
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)

For example:

FILE_NAME                                           SHRINK_TO CURRENT_SIZE    SAVINGS
-------------------------------------------------- ---------- ------------ ----------
/u01/app/oracle/oradata/PHIL112/system01.dbf              696          700          4
/u01/app/oracle/oradata/PHIL112/sysaux01.dbf              579          610         31
/u01/app/oracle/oradata/PHIL112/users01.dbf                85           93          8
/u01/app/oracle/oradata/PHIL112/undotbs01.dbf              65          120         55

SQL>

To resize, use:

alter database datafile '/path/to/data/file.dbf' resize 100m;

The next option you have is to create a new tablespace (and associated datafile(s)) and move the current database objects to the new tablespace. You can do this by using datapump, or do it manually. Tim Hall has an excellent article on how to perform this here.

How to load .dbf to run select queries

Toad cannot directly read Oracle data files, no. You would need to recover the database on a local server. Once you do that, you could connect to Oracle database on the local server from Toad and run whatever queries you'd like.

Assuming that what you've been given is a consistent cold backup of your database, you'd need, at a minimum, exactly the same version of Oracle (patchsets included) on exactly the same operating system that the third party vendor uses. Assuming that the vendor is vaguely competent about applying patches, that will likely require that you have a My Oracle Support account which would require that your organization has appropriate Oracle licenses. How you do that will depend on the operating system your Oracle server will be using.

Since it sounds like you are not a DBA and you're not particularly familiar with Oracle, assuming there is a reason that you can't simply access the existing database to run whatever queries you need to run, I would tend to suspect that you would be much better off requesting an export of the database (or potentially the tables you're interested in if you really care about a subset of the data) rather than a backup. Toad can't read an export directly either but it is a lot easier to import and export file into a database you create on your local server without worrying about matching the version and operating system that the vendor uses or going through the effort of doing a proper database restore.

Best Answer

Related Solutions

Windows – How do we free up disk space from allocated namespace? / ORA-03297

How to load .dbf to run select queries

Related Question