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

The Oracle (tm) Users' Co-Operative FAQ

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

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	|          |  <--- >
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
	FROM 	DBA_FREE_SPACE fs, DBA_DATA_FILES df
	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 :
	TABLESPACE_NAME FILE_NAME                       FRAGMENTS   TOTAL_KB BIGGEST_KB
	--------------- ------------------------------ ---------- ---------- ----------
	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
	FROM DBA_FREE_SPACE fs, DBA_DATA_FILES df 
	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
	TABLESPACE_NAME FILE_NAME                      START_BLOCK NUM_BLOCKS  END_BLOCK         KB
	--------------- ------------------------------ ----------- ---------- ---------- ----------
	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 :

  
alter tablespace xxxxx coalesce; This combines any adjacent free blocks into a single one. This is the job that SMON tries to do if the tablespace has been created with a pctincrease which is non-zero. If you set the value to zero, you must manually coalesce the tablespaces at a time most convenient to your database. This is sometimes all that is required when an object cannot be created as it's initial extent is bigger than the biggest chuck of free space in the tablespace.
  
From Oracle 8i onwards, tables can be rebuilt into another tablespace using the alter table xxx move tablespace yyy; and alter index xxx rebuild tablespace yyy;commands. While this doesn't get rid of the fragmentation, it can help by moving tables out until such time as you can free up the space by coalescing. They can then be moved back in again. This is only helpful is there are a small number of tables, and they are not too large.
  
Prior to Oracle 8i, and if there are too many fragmented objects, then you need to :
	

  		
  export the affected tables and indexes 
  		
  drop them 
  		
  rebuild the tablespace with correct default storage values - see below
  		
  possibly create the tables and indexes with better storage settings - if you can't let them default to the tablespace defaults 
  		
import the objects back in again. Don't forget to export with no compression - see below
	

Avoiding free space fragments

  
Always specify zero for pctincrease. This stops Oracle adjusting the next value and stops odd sized extents appearing.
  
Don't forget to manually coalesce your tablespaces. A cron script could be run to do this in a pseudo SMON manner.
  
It is best to create tablespace default storage settings and have all objects in that tablespace default to those, rather than their own individual values. This means that every object in the tablespace has the same extent size, so as extents are dropped, the free space is exactly the right size to be used by another object.
  
If you absolutely must create an object with its own storage settings, then always create objects with identical initial and next sizes, and try to keep them out of tablespaces which have been set up as above. This will stop you from creating FSF in those tablspaces.
  
Use locally managed tablespaces with uniform extent sizes. This way, no-one can create objects with odd sized extents in the tablespace.
  
When exporting objects, always specify compress=no on the command line. Export defaults (wrongly in my opinion) to compressing all the data in a table or index into a single initial extent. This destroys any attempt by the DBA to maintain uniform extent sizes withing a tablespace.
  
If you have no control over the export process, create the objects before you import them, and specify ignore=yes on the command line when importing. This causes Oracle to ignore the setting in the export file for creating the objects, and simply imports the data into the already existing, and correctly configured, objects.


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 http://www.orapub.com (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).




复制