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.

3 - 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 DBA_FREE_SPACE view describes the free extents in all tablespaces in the database.

4 - 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

5 - Tutorial on Tablespace

5.1 - 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

5.2 - 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: $\frac{\displaystyle 704}{\displaystyle 64} = 11$

extends

• Oracle use one extend (64k block of space) to manage the locally tablespaces in the datafile, their is then $11 - 1 = 10$

extends available for data.

5.3 - 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

5.4 - 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:

$184k = 23 \text{ last used block } * 8k \text{ block size}$

is the high water mark

Go back:

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

5.5 - T1 grows

Table T1 grows and we have:

XYXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXfff
INSERT INTO t1 (x) SELECT rownum FROM all_objects WHERE rownum <= 56;
56 rows created.

5.5.1 - 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

5.5.2 - 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;

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

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

5.8 - 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

5.8.1 - 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

6 - Documentation / Reference

db/oracle/free_space.txt · Last modified: 2017/09/06 19:30 by gerardnico