Resize Datafile to Reduce Gaps and release free space for others-Oracle

If there are many inserts and deletes in the database, some datafile becomes gapped.

Which means, the following:

X is busy block and 0 is free block

Picture 1.

You can determine how much free space is in datafile:

SQL> SELECT a.file_id
  2        ,b.file_name
  3        ,SUM(a.bytes)/1024/1024 AS "Free MB"
  4  FROM dba_free_space a
  5      ,dba_data_files b
  6  WHERE a.file_id=b.file_id
  7  GROUP BY a.file_id,b.file_name
  8  ORDER BY 3 DESC
  9  /

   FILE_ID FILE_NAME                                            Free MB
---------- ---------------------------------------------------- ----------
         2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF  914.5
         1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF   382.4375
         6 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TESTTBS.DBF    19.9375
         3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF   13.625
         5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF  3.625
         4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF    2.1875

6 rows selected

As you can see UNDOTBS01.DBF and SYSTEM01.DBF have biggest free spaces, but it’s better not to touch them.

Let’s choose TESTTBS.DBF datafile.

Calculate the total size of this datafile:

SQL> SELECT file_id,bytes/1024/1024 AS "Size MB"
  2  FROM dba_data_files
  3  WHERE file_id=6
  4  /

   FILE_ID    Size MB
---------- ----------
         6         20

Used Size Calculation: 20MB(Total)-19MB(Free)=1MB(Used)

SQL> ALTER DATABASE DATAFILE 6 RESIZE 1M;

Database altered

Everything is OK, but there may happen the following situation

Let’s choose another datafile EXAMPLE01.DBF and see the total size of it:

SQL> SELECT file_id,bytes/1024/1024 AS "Size MB"
  2      FROM dba_data_files
  3      WHERE file_id=5
  4  /

   FILE_ID    Size MB
---------- ----------
         5         81

Used Size Calculation: 81MB(Total)-3MB(Free)=78MB(Used)

SQL> ALTER DATABASE DATAFILE 5 RESIZE 78M;

ALTER DATABASE DATAFILE 5 RESIZE 78M

ORA-03297: file contains used data beyond requested RESIZE value

This shows that EXAMPLE01.DBF datafile is gapped, as it is shown on the Picture 1.

Let’s see what blocks are free:

SQL> SELECT file_id
  2        ,block_id
  3        ,bytes
  4  FROM dba_free_space
  5  WHERE file_id=5
  6  /

   FILE_ID   BLOCK_ID      BYTES
---------- ---------- ----------
         5        425    2490368
         5       1313     458752
         5      10265     851968

Picture describing the current situation:

Size Calculation: 81(Total size MB)*1024*1024=84934656(Bytes)
In block 10265-free space is 851968(Bytes)
84934656(Bytes)-851968(Bytes)=84082688(Bytes)

SQL>  ALTER DATABASE DATAFILE 5 RESIZE 84082688;

Database altered

Now our datafile looks like that:

If this is not enough and you still want to reduce datafile size to be just used space in it, then you may do the following:

Identify objects that are located in the blocks from 425, drop them, reduce datafile size till 424 block, recreate objects…not good yes?!

The steps described in this post would be very useful if there was a lot of(or only) free space after the last block(In our case after 10264).

The best way it to create new datafile with the actual used size in your gapped datafile, export objects from old one and then import these objects to the new file.

The moral of this post is to show the logic, in what situation these steps are useful and what is the alternative solution.

I hope it was helpful for you