The Oracle (tm) Users' Co-Operative FAQ

How can I reclaim unusable fragments of space in a tablespace ?

Author's name: Norman Dunbar;

Author's Email:

Date written: 02/07/2001

Oracle version(s): 7.3.4 onwards

How do I go about reclaiming fragments of free space in my tablespaces and how can I avoid creating them in the first place.

Back to index of questions

What is free space fragmentation?

Free space fragmentation is a situation that developes in a tablespace, where 'holes' appear in the tablespace. These holes are made up of free blocks, but no objects attempt to use them when they need to extend. They are created by the process of adding and deleting extents from the objects in the tablespace, where these objects have diferent initial and/or next extent sizes.

Having the object or the tablespace use a non-zero pctincrease value is a great way to cause this problem as well. There is no known need to have the pctincrease setting anything other than zero.

An example of how to create FSF. Imagine a tablespace which is 100Mb in size, and has nothing in it. A table is then created with an initial size of 20Mb and a next size of 12 Mb, min extents is set to 6. This uses up 80Mb of the tablespace in one swoop. Next an table with a different extent size is created, say initial = 22M, next = 7M minextents = 1 and over a period of time, this table grows in size. Each time it grows it needs an extra 7M of space in the tablespace.

If the first object has some data deleted from it, and drops one of it's extents as a result, then a block of 20M is added to the list of free space for this tablespace and it can be used by any other object. If the second table needs an extra extent, if might use 7M of the 20M freed by the first table - leaving a free block of 13M. This is now too small for the first table to reuse as it needs 20M, but will allow the second table to use another 7M of it, leaving 6M free. Unfortunately, this 6M is too small to be used by either table and so just sits there unused forever.

The following shows the state of the 20M extent that was dropped by the first table and re-used by the second, there are two chunks of 7M and one of 6M. The 6M is never going to be used by these two tables and is effectively wasted space.

	20	+----------+
	19	|          |
	18	|          |
	17	|          |  <--- This block of 6M is 'wasted' space as neither table has a 6M 'next' size.
	16	|          |
	15	|          |
	14	+----------+
	13	|          |
	12	|          |
	11	|          | <--- This 7M block has been reused by table 2 as a 'next' extent.
	10	|          |
	 9	|          |
	 8	|          |
	 7	+----------+
	 6	|          |
	 5	|          |
	 4	|          | <--- This 7M block has been reused by table 2 as a 'next' extent.
	 3	|          |
	 2	|          |
	 1	+----------+

Detecting free space fragments

The easiest way to find out if your tablespaces are fragmenting, and what size each chunk of free space is, is to run the following script :

	column tablespace_name format a15
	column file_name format a30
	SELECT 	fs.tablespace_name, df.file_name, COUNT(*) AS fragments, 
	ROUND(SUM(fs.bytes)/1024,2) AS total_kb, ROUND(MAX(fs.bytes)/1024,2) AS biggest_kb
	WHERE 	fs.file_id(+) = df.file_id
	GROUP BY fs.tablespace_name,df.file_name;

The output from the above script will look something similar to the following :

	--------------- ------------------------------ ---------- ---------- ----------
	CC              /data2/DBADMIN/cc01.dbf                 1     102352     102352
	CCINDX          /data2/DBADMIN/cc01indx.dbf             1     102392     102392
	RBS             /data2/DBADMIN/rbs01.dbf                1      18072      18072
	SYSTEM          /data2/DBADMIN/system01.dbf             1      90992      90992
	TEMP            /data2/DBADMIN/temp01.dbf              25      81912      30952
	TESTS           /data2/DBADMIN/users01.dbf           1576     101112        952

The above script will give you a quick look at how many fragments there are in your tablespaces free space. But it will not identify which chunk is what size. To do this, you need the following script which shows all the free blocks in a specific tablespace. Tablespace TESTS appears to be in a bad way! The following script will identify the free blocks in a specific tablespace :

	SET pages 40
	SET lines 132
	COLUMN tablespace_name format a15
	COLUMN file_name format a30
	SELECT fs.tablespace_name, df.file_name, fs.block_id start_block, 
	fs.blocks num_blocks, fs.block_id + fs.blocks -1 end_block, fs.bytes/1024 kb
	WHERE fs.tablespace_name = UPPER('&tablespace_name')
	AND fs.file_id = df.file_id
	ORDER BY tablespace_name, file_name, start_block DESC;
Enter value for tablespace_name: tests

	--------------- ------------------------------ ----------- ---------- ---------- ----------
	TESTS           /data2/DBADMIN/users01.dbf           12182        119      12300        952
	TESTS           /data2/DBADMIN/users01.dbf              84         20        103        160

Reclaiming free space fragments

Reclaiming the free space can be relatively simple, but can involve a lot of work during 'unsociable hours' if various objects need to be exported and imported again :

Avoiding free space fragments

Further reading:

Practical Oracle 8i Building Efficient Databases by Jonathan Lewis, Chapter 8 Leveraging Tablespaces.

Oracle 8 Administrator's Guide.

All About Oracle Database Fragmentation - Craig Shallahamer on (you need to sign up for membership - which is free)

How To Stop Defragmenting And Start Living: The Definitive Word On Fragmentation - Himatsingka & Loaiza, available on MetaLink (which needs a logon, but is free to sign up to).

Back to top

Back to index of questions