Oracle Database - (Fragmented) (Unused|Free) space (Reclaim|Shrink|resize)

Used and free space.

A data file contains ordered extents (by block Id).

One segments is made up of one of several extents (not ordered, contiguous).

Over time, updates and deletes on objects within a tablespace can create pockets of empty space that individually are not large enough to be reused for new data. This type of empty space is referred to as fragmented free space.

For a table: once an extent is allocated to a table segment – it will belong to that table until:

• the table is dropped,
• the table is truncated,
• or the extent is explicitly (deallocate|shrink) (which only works if the extent NEVER contained any data whatsoever)

This is totally expected. the space is there, the space is available, the space is free for use but ONLY by this segment. The tablespace will not change in regards to free/used space – the extent belongs to that table and will stay with that table. It is however FREE SPACE in the table.

The DBA_FREE_SPACE view describes the free extents in all tablespaces in the database.

Space used and free by tablespace (from SQL Developer)

SELECT   ts.tablespace_name
  , 'SQLDEV:GAUGE:0:100:0:0:'
    ||NVL ( ROUND ( ( ( datafile.bytes - NVL ( freespace.bytes, 0 ) ) / datafile.bytes ) * 100, 2 ), 0 ) percent_used
  , ROUND ( ( ( datafile.bytes         - NVL ( freespace.bytes, 0 ) ) / datafile.bytes ) * 100, 2 ) PCT_USED
  , datafile.bytes                     / 1024 / 1024 allocated
  , ROUND ( datafile.bytes             / 1024 / 1024 - NVL ( freespace.bytes, 0 ) / 1024 / 1024, 2 ) used
  , ROUND ( NVL ( freespace.bytes, 0 )  / 1024 / 1024, 2 ) free
  , datafile.datafiles
  FROM dba_tablespaces ts
  , (SELECT   tablespace_name
      , SUM ( bytes ) bytes
      FROM dba_free_space
      GROUP BY tablespace_name
    ) freespace
  , (SELECT   COUNT ( 1 ) datafiles
      , SUM ( bytes ) bytes
      , tablespace_name
      FROM dba_data_files
      GROUP BY tablespace_name
    ) datafile
  WHERE freespace.tablespace_name (+) = ts.tablespace_name
  AND datafile.tablespace_name (+)   = ts.tablespace_name
  ORDER BY NVL ( ( ( datafile.bytes - NVL ( freespace.bytes, 0 ) ) / datafile.bytes ), 0 ) DESC

Two tables T1 and T2 in one tablespace.

• T1 is represented by X
• T2 is represented by Y
• free space in the tablespace is represented by f

Database version: 12.1.0.2.0

create tablespace shrink_me
datafile '/tmp/shrink_me.dbf' size 704k -- data file size
segment space management manual
uniform size 64k -- extend size

• There is then: <math>\frac{\displaystyle 704}{\displaystyle 64} = 11</math> extends
• Oracle use one extend (64k block of space) to manage the locally tablespaces in the datafile, their is then <math>11 - 1 = 10</math> extends available for data.

WITH ts AS
    (SELECT   tablespace_name
      , block_size
      FROM dba_tablespaces
      WHERE tablespace_name = 'SHRINK_ME'
    )
  , freeUsed AS
    (SELECT   ex.file_id
      , segment_name
      , ex.extent_id
      , ts.block_size
      , ex.block_id first_block
      , ex.block_id + blocks - 1 last_block
        --hwm
      FROM dba_extents ex
      , ts
      WHERE ex.tablespace_name = ts.tablespace_name
    UNION ALL
    SELECT   fs.file_id
      , 'free'
      , NULL
      , ts.block_size
      , fs.block_id
      , fs.block_id + blocks - 1
      FROM dba_free_space fs
      , ts
      WHERE fs.tablespace_name = ts.tablespace_name
    )
  SELECT   freeUsed.*
      --, 'alter database datafile ''/tmp/shrink_me.dbf'' resize ' || last_block * block_size / 1024 || 'k;' as cmd
    FROM freeUsed
    ORDER BY file_id
    , first_block;

initial Space:

   FILE_ID BLOCK_SIZE FIRST_BLOCK LAST_BLOCK SEGMENT_NA
---------- ---------- ----------- ---------- ----------
        43       8192           8         87 free

You created T1 and T2, your datafile in that tablespace might look like this sequence of extents:

XYffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff

create table t1 ( 
       x int, -- number(38)
       a char(2000) default 'a', 
       b char(2000) default 'b', 
       c char(2000) default 'c') 
tablespace shrink_me;

create table t2 ( 
       x int, -- number(38)
       a char(2000) default 'a', 
       b char(2000) default 'b', 
       c char(2000) default 'c') 
tablespace shrink_me;

Each row in these tables will consume a block (8 rows/extent - but don't forget the first block is borrowed by Oracle to manage space in the segment…)

Space

FILE_ID    BLOCK_SIZE FIRST_BLOCK LAST_BLOCK SEGMENT_NAME 
---------- ---------- ----------- ---------- -------------
        42       8192           8         15 T1           
        42       8192          16         23 T2           
        42       8192          24         87 free              

Shrink You can shrink that file and get rid of all of the f's.

ALTER database datafile '/tmp/shrink_me.dbf' resize 184k;

where: <math>184k = 23 \text{ last used block } * 8k \text{ block size}</math> is the high water mark

Go back:

alter database datafile '/tmp/shrink_me.dbf' resize 704k; 

Table T1 grows and we have:

XYXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXfff

insert into t1 (x) select rownum from all_objects where rownum <= 56;

56 rows created.

   FILE_ID SEGMENT_NA  EXTENT_ID BLOCK_SIZE FIRST_BLOCK LAST_BLOCK
---------- ---------- ---------- ---------- ----------- ----------
        43 T1                  0       8192           8         15
        43 T2                  0       8192          16         23
        43 T1                  1       8192          24         31
        43 T1                  2       8192          32         39
        43 T1                  3       8192          40         47
        43 T1                  4       8192          48         55
        43 T1                  5       8192          56         63
        43 T1                  6       8192          64         71
        43 T1                  7       8192          72         79
        43 free                        8192          80         87      

By shrinking that file, we can get rid of just three f's (rest of the file is full of data).

ALTER database datafile '/tmp/shrink_me.dbf' resize 632k;

Database altered.

where: 632 = 79 * 8 k

Go Back:

ALTER database datafile '/tmp/shrink_me.dbf' resize 704k;

If we dropped T1, all of the T1's would become free space and we could shrink the file.

drop table t1;

All extents allocated for a table that is dropped are returned to the free space of the tablespace and can be used by any other object requiring new extents or new objects. All rows corresponding to a clustered table are deleted from the blocks of the cluster.

Space

   FILE_ID BLOCK_SIZE FIRST_BLOCK LAST_BLOCK SEGMENT
---------- ---------- ----------- ---------- -------
        42       8192           8         15 free
        42       8192          16         23 T2
        42       8192          24         31 free  <-- free but not purged
        42       8192          32         39 free  <-- free but not purged
        42       8192          40         47 free  <-- free but not purged
        42       8192          48         55 free  <-- free but not purged
        42       8192          56         63 free  <-- free but not purged
        42       8192          64         71 free  <-- free but not purged
        42       8192          72         79 free  <-- free but not purged
        42       8192          80         87 free

Shrink But When you drop a table, normally the database does not immediately release the space associated with the table. Rather, the database renames the table and places it in a recycle bin, where it can later be recovered with the FLASHBACK TABLE statement if you find that you dropped the table in error. If you should want to immediately release the space associated with the table at the time you issue the DROP TABLE statement, include the PURGE clause as shown in the following steps.

alter database datafile '/tmp/shrink_me.dbf' resize 184k

Error report -
SQL Error: ORA-03297: file contains used data beyond requested RESIZE value
03297. 00000 -  "file contains used data beyond requested RESIZE value"
*Cause:    Some portion of the file in the region to be trimmed is
           currently in use by a database object
*Action:   Drop or move segments containing extents in this region prior to
           resizing the file, or choose a resize value such that only free
           space is in the trimmed.

PURGE TABLE t1;

Space

   FILE_ID BLOCK_SIZE FIRST_BLOCK LAST_BLOCK SEGMENT
---------- ---------- ----------- ---------- -------
        42       8192           8         15 free
        42       8192          16         23 T2
        42       8192          24         87 free

Shrink

alter database datafile '/tmp/shrink_me.dbf' resize 184k;

Database altered.

You can STILL only shrink the free's away at the end - there is a T2 extent way out there and we cannot shrink over it. What you can do is:

• move the data to a new tablespace
• or export/import of the data in a clean tablespace

What you can do is, move the data to a new tablespace:

CREATE tablespace shrink_me2
datafile '/tmp/shrink_me2.dbf' size 704k -- data file size
segment SPACE management manual
uniform size 64k -- extend size

alter table t2 MOVE tablespace shrink_me2

Space shrink_me:

   FILE_ID BLOCK_SIZE FIRST_BLOCK LAST_BLOCK SEGMENT_NA
---------- ---------- ----------- ---------- ----------
        43       8192           8         87 free

Space shrink_me2:

   FILE_ID BLOCK_SIZE FIRST_BLOCK LAST_BLOCK SEGMENT_NA
---------- ---------- ----------- ---------- ----------
        44       8192           8         15 T2
        44       8192          16         87 free

• Free space Management
• Ask Tom Reclaim space
• Free space
• Database Administrator's Guide - Reclaiming Wasted Space
• Reclaiming Unused Space in Datafiles