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

disk-spaceoracleoracle-10gwindows

We are running out of disk space.

The server has a 420 GB HDD, and runs a Microsoft SQL Server and an Oracle Server(10g).

The Oracle server uses up to 315 GB space. We have just loaded a 100GB Microsoft SQL Server database. Now we have approximately 5GB free and need to make a backup of the SQL Server database.

First we tried to move the backup to an other drive but this didn't work.

Then we tried to resize the allocated space of our Oracle Server, which consists of 6 files of 20GB each with about 4GB used in each file. When we tried to resize this file to 10GB, we got the Oracle Error ORA-03297 but it shows 4GB used. After several tries we could shorten some files to 16/17/18GB, but we are still in need of more space.

Is there a Solution how we can resize the Oracle files to 10GB each?

We've tried Coalesce and deallocate unused space, however that did not work either.

Best Answer

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.

Related Question