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