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

Card Puncher Data Processing

About

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).

Oracle Segment Extent Data Block

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.

Free space definition

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 apps/search/search.jsp view describes the free extents in all tablespaces in the database.

Data Dictionary

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

Tutorial on Tablespace

Prerequisites

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

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.

Space calculation

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

First table creation

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; 

T1 grows

Table T1 grows and we have:

XYXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXfff
insert into t1 (x) select rownum from all_objects where rownum <= 56;
56 rows created.

Space

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      

Extent_id is the id of the extent in the segment

Shrink

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;

Drop T1

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

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.

Final Shrink

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

Table T2 Move

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

Documentation / Reference





Discover More
Card Puncher Data Processing
Oracle Database - Data Files

Every Oracle database has one or more physical datafiles (OS File), which contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the...
Card Puncher Data Processing
Oracle Database - Direct (path insert|load) (/*+ APPEND */)

A direct-path insert is also known as: direct load A direct-path insert is a bulk operation which will only bypass redo log generation in three cases : the database is in NOARCHIVELOG mode database...
Card Puncher Data Processing
Oracle Database - High Water Mark (HWM) - boundary between used and unused space

The High water mark is the boundary between used and unused space: for a data file. See or for a segment You can think at the high-water mark (HWM) as the rightmost block that ever contained data...
Oracle Segment Extent Data Block
Oracle Segment - (Free) Space Management (ASSM) of a Tablespace

In a locally managed tablespace, there are two methods that Oracle Database can use to manage segment space: automatic and manual. To manage free space in the segment: Manual segment space management...



Share this page:
Follow us:
Task Runner