Reclaiming Unused Space in Datafiles : Part -2

Here is Reclaiming Unused Space in Datafiles : Part - 1



There are a number of scenarios that can lead to unused space in datafiles. The two most common I see are:

• A lack of housekeeping/maintenance means that one or more tables have grown excessively. After the data is pruned the datafiles contain unused space that needs to be reclaimed.
• One or more segments (tables, partitions or indexes) have been moved to another tablespace leaving empty areas in the datafiles that previously held them.

 Here is Reclaiming Unused Space in Datafiles : Part - 1 
 where these were discussed

Setup Test Environment

Identify Tablespaces with Free Space

Export/Import

Manual Tablespace Reorganization

Enterprise Manager Tablespace Reorganization

In this article I will discuss a few of the ways of reclaiming this unused space

• Shrink?
• Tablespaces with Multiple Datafiles
• Undo Tablespace
• Temp Tablespace
• SYSAUX Tablespace

Shrink?

If there is only a single object in the datafile, it's possible a online shrink operation will actually be enough to compact the data and free up the empty blocks, allowing the datafile to be resized to a smaller size.

Remember, the shrink only compacts the data. It doesn't guarantee the blocks will be placed at the front of the datafile, so it's possible you will have blocks at the end of the datafile, which means the datafile can't be resized smaller. If this is the case you will need to use one of the methods mentioned previously.

Tablespaces with Multiple Datafiles

So far I've conveniently side-stepped the issue of tablespaces with multiple datafiles. Why? Because it makes the tablespace extent map a little more confusing to look at. To see what I mean, perform the setup again, but this time before building the tables add an additional datafile to the tablespace.

ALTER TABLESPACE reclaim_ts ADD DATAFILE '/u01/app/oracle/oradata/DB11G/reclaim02.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M;

With the tables built, populated and the "T1" table truncated, the extent map will look something like this.




The problem here is we can't tell what files the gaps are in without hovering over the gaps and reading the tool tip. That's not very simple when we have lots of files, segments and gaps to contend with. At this point I stop looking at the extent map and just use a script to identify all the gaps in the tablespace, or individual datafiles.

The ts_extent_map.sql script produces a list of all the free space in the tablespace or individual datafile. The combined output and separate output for each datafile is shown below.

SQL> @ts_extent_map reclaim_ts all

Tablespace Block Size (bytes): 8192

*** GAP *** (23 -> 896) FileID=8 Blocks=872 Size(MB)=6.81

*** GAP *** (959 -> 1024) FileID=8 Blocks=64 Size(MB)=.5

*** GAP *** (127 -> 768) FileID=9 Blocks=640 Size(MB)=5

Total Gap Blocks: 1576

Total Gap Space (MB): 12.31

SQL> @ts_extent_map reclaim_ts 8

Tablespace Block Size (bytes): 8192

*** GAP *** (23 -> 896) FileID=8 Blocks=872 Size(MB)=6.81

*** GAP *** (959 -> 1024) FileID=8 Blocks=64 Size(MB)=.5

Total Gap Blocks: 936

Total Gap Space (MB): 7.31

SQL> @ts_extent_map reclaim_ts 9

Tablespace Block Size (bytes): 8192

*** GAP *** (127 -> 768) FileID=9 Blocks=640 Size(MB)=5

Total Gap Blocks: 640

Total Gap Space (MB): 5

SQL>

This can help you make a judgment as to whether a tablespace reorganization is necessary.

Undo Tablespace

The simplest way to reclaim space from the undo tablespace is to create a new undo tablespace, make it the database undo tablespace and drop the old tablespace. In the following example I've used autoextend, but you may wish to remove this if you want manual control over the datafile size.

CREATE UNDO TABLESPACE undotbs2 DATAFILE '/u01/app/oracle/oradata/DB11G/undotbs02.dbf' SIZE 2G AUTOEXTEND ON NEXT 1M;

ALTER SYSTEM SET UNDO_TABLESPACE=undotbs2;

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

Remember, flashback operations requiring undo will not be possible because you have deleted the retained undo. You may want to consider your UNDO_RETENTION parameter setting.

Temp Tablespace

If you are using oracle 11g, you can shrink a temporary tablespace using the ALTER TABLESPACE command.

If you are using a database version prior to 11g, reducing the size of the temporary tablespace is similar to reclaiming space from the undo tablespace. Create a new temp tablespace, move the users on to it, then drop the old temp tablespace.

CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u01/app/oracle/oradata/DB11G/temp02.dbf' SIZE 2G AUTOEXTEND ON NEXT 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

-- Switch all existing users to new temp tablespace.

BEGIN

  FOR cur_user IN (SELECT username FROM dba_users WHERE temporary_tablespace = 'TEMP') LOOP

    EXECUTE IMMEDIATE 'ALTER USER ' || cur_user.username || ' TEMPORARY TABLESPACE temp2';

  END LOOP;

END;

/

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

SYSAUX Tablespace

The SYSAUX tablespace is similar to any other tablespace, so the techniques discussed in this article will work on the SYSAUX tablespace too. If you've moved some of the occupants to a new tablespace, you may want to reduce the size of the SYSAUX tablespace to recovery the room you've saved. Options include the following.

• Online shrink of objects towards the end of the datafile may allow you to reduce the datafile size.
• Move the SYSAUX objects with extents towards the end of the datafile to a temporary tablespace, reduce the datafile size and move them back.