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.

Block Alocation in Datafile(Gapped Datafile)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:

Block Alocation in Datafile(Gapped Datafile)2

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:

Block Alocation in Datafile(Gapped Datafile)3

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?!Smile

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 youSmile

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: