Oracle Database frees only unused space above the high water mark (that is, the point beyond which database blocks have not yet been formatted to receive data). Oracle deallocates unused space beginning from the end of the object and moving toward the beginning of the object to the high water mark. If an extent is completely contained in the deallocation, then the whole extent is freed for reuse. If an extent is partially contained in the deallocation, then the used part up to the high water mark becomes the extent, and the remaining unused space is freed for reuse.In this Blog, we will make deallocation tests and look its effect in relation to low high-water mark (LHWM) and high high-water mark (HHWM). Then we dump LMT file space header and segment header to reveal extent management internals.
Note: Tested in Oracle 19.8
1. Deallocation Test
At first, we make a test:
-- drop tablespace test_ts including contents and datafiles;
-- Locally Managed, AUTOALLOCATE (system managed) Tablespace, Segment Space Management AUTO
SQL> create BIGFILE tablespace test_ts datafile '/oratestdb/oradata/testdb/test_ts.dbf' size 1000m online
extent management local autoallocate segment space management auto;
-- drop table test_tab purge;
SQL> create table test_tab tablespace test_ts as select level x, rpad('ABC', 1000, 'X') y from dual connect by level <= 2*1e4;
Table created.
SQL> select round(bytes/1024/1024) mb, blocks, extents, initial_extent/8192 initial_ext_blocks, next_extent/8192 next_ext_blocks
from dba_segments where segment_name = 'TEST_TAB';
MB BLOCKS EXTENTS INITIAL_EXT_BLOCKS NEXT_EXT_BLOCKS
---------- ---------- ---------- ------------------ ---------------
23 2944 38 8 128
SQL> select blocks, count(*) cnt, blocks*count(*) blocks#
,min(extent_id) min_ext_id, max(extent_id) max_ext_id, max(extent_id)-min(extent_id)+1 ext_id_span
,min(block_id) min_id_blk, max(block_id)+blocks-1 max_id_blk, max(block_id)+blocks-1-min(block_id)+1 blk_id_span
from dba_extents where segment_name = 'TEST_TAB' group by blocks order by 1;
BLOCKS CNT BLOCKS# MIN_EXT_ID MAX_EXT_ID EXT_ID_SPAN MIN_ID_BLK MAX_ID_BLK BLK_ID_SPAN
---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- -----------
8 16 128 0 15 16 8696 8823 128
128 22 2816 16 37 22 8832 11647 2816
Now we deallocate unused space:
-- 'keep 0' has no effect since it is the default
SQL> alter table test_tab deallocate unused keep 0;
Table altered.
SQL> select blocks, count(*) cnt, blocks*count(*) blocks#
,min(extent_id) min_ext_id, max(extent_id) max_ext_id, max(extent_id)-min(extent_id)+1 ext_id_span
,min(block_id) min_id_blk, max(block_id)+blocks-1 max_id_blk, max(block_id)+blocks-1-min(block_id)+1 blk_id_span
from dba_extents where segment_name = 'TEST_TAB' group by blocks order by 1;
BLOCKS CNT BLOCKS# MIN_EXT_ID MAX_EXT_ID EXT_ID_SPAN MIN_ID_BLK MAX_ID_BLK BLK_ID_SPAN
---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- -----------
8 16 128 0 15 16 8696 8823 128
96 1 96 37 37 1 11520 11615 96
128 21 2688 16 36 21 8832 11519 2688
Oracle has two High-Water Marks:
-. Low High-Water Mark (LHWM): Like the old High Water Mark. All blocks below LHWM have been formatted for the table. -. High High-Water Mark (HHWM): blocks between LHWM and HHWM are formatted only when used, some blocks could be unformatted.The last extent is located between LHWM and HHWM, and it can contain blocks which have not yet been formatted, hence to be deallocated. In the above Oracle Docu excerpt, high water mark probably refers to Low High-Water Mark (LHWM).
After deallocation, total extents is still 38 (16+1+21), but blocks is reduced to 2912 (128+96+2688) blocks from previous 2944 blocks. In the later TEST_TAB segment header dump, we will see following text:
Low HighWater Mark :
Highwater:: 0x00002d60 ext#: 37 blk#: 96 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 2912
SQL> insert into test_tab values (987654, rpad('ABC', 1000, 'X'));
1 row created.
SQL> commit;
Commit complete.
SQL> select blocks, count(*) cnt, blocks*count(*) blocks#
,min(extent_id) min_ext_id, max(extent_id) max_ext_id, max(extent_id)-min(extent_id)+1 ext_id_span
,min(block_id) min_id_blk, max(block_id)+blocks-1 max_id_blk, max(block_id)+blocks-1-min(block_id)+1 blk_id_span
from dba_extents where segment_name = 'TEST_TAB' group by blocks order by 1;
BLOCKS CNT BLOCKS# MIN_EXT_ID MAX_EXT_ID EXT_ID_SPAN MIN_ID_BLK MAX_ID_BLK BLK_ID_SPAN
---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- -----------
8 16 128 0 15 16 8696 8823 128
96 1 96 37 37 1 11520 11615 96
128 22 2816 16 38 23 8832 11775 2944
SQL> alter table test_tab deallocate unused;
Table altered.
SQL> select blocks, count(*) cnt, blocks*count(*) blocks#
,min(extent_id) min_ext_id, max(extent_id) max_ext_id, max(extent_id)-min(extent_id)+1 ext_id_span
,min(block_id) min_id_blk, max(block_id)+blocks-1 max_id_blk, max(block_id)+blocks-1-min(block_id)+1 blk_id_span
from dba_extents where segment_name = 'TEST_TAB' group by blocks order by 1;
BLOCKS CNT BLOCKS# MIN_EXT_ID MAX_EXT_ID EXT_ID_SPAN MIN_ID_BLK MAX_ID_BLK BLK_ID_SPAN
---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- -----------
8 16 128 0 15 16 8696 8823 128
96 1 96 37 37 1 11520 11615 96
128 22 2816 16 38 23 8832 11775 2944
In fact, if we did not perform any deallocation, we could safely insert 200 more rows without adding any new extent:
insert into test_tab select level x, rpad('ABC', 1000, 'X') y from dual connect by level <= 200;
The above test shows that deallocation frees the unformatted blocks of last extent in the first time, and no more further effect in the next execution.
2. File Header and Bitmap Dump
Now we create 3 tablesapces, one is BIGFILE, one is SMALLFILE, and one more BIGFILE with "_enable_12g_bft"=FALSE (default is TRUE). On each of them, we also create one table.
drop tablespace test_ts including contents and datafiles;
create BIGFILE tablespace test_ts datafile '/oratestdb/oradata/testdb/test_ts.dbf' size 1000m online
extent management local autoallocate segment space management auto;
drop table test_tab purge;
create table test_tab tablespace test_ts as select level x, rpad('ABC', 1000, 'X') y from dual connect by level <= 2*1e4;
drop tablespace test_ts_small including contents and datafiles;
create SMALLFILE tablespace test_ts_small datafile '/oratestdb/oradata/testdb/test_ts_small.dbf' size 1000m online
extent management local autoallocate segment space management auto;
drop table test_tab_small purge;
create table test_tab_small tablespace test_ts_small as select level x, rpad('ABC', 1000, 'X') y from dual connect by level <= 2*1e4;
--alter system reset "_enable_12g_bft" scope=memory; -- (enable 12g bigfile tablespace, default TRUE)
alter system set "_enable_12g_bft"=FALSE scope=memory;
drop tablespace test_ts_12g_bft_false including contents and datafiles;
create BIGFILE tablespace test_ts_12g_bft_false datafile '/oratestdb/oradata/testdb/test_ts_12g_bft_false.dbf' size 1000m online
extent management local autoallocate segment space management auto;
drop table test_tab_12g_bft_false purge;
create table test_tab_12g_bft_false tablespace test_ts_12g_bft_false as select level x, rpad('ABC', 1000, 'X') y from dual connect by level <= 2*1e4;
alter system reset "_enable_12g_bft" scope=memory;
SQL > select tablespace_name, file_id from dba_data_files where tablespace_name in ('TEST_TS', 'TEST_TS_SMALL', 'TEST_TS_12G_BFT_FALSE');
TABLESPACE_NAME FILE_ID
------------------------------ ----------
TEST_TS 1968
TEST_TS_SMALL 1969
TEST_TS_12G_BFT_FALSE 1970
alter session set tracefile_identifier = 'TEST_TS';
alter system dump datafile 1968 block min 2 block max 3;
alter session set tracefile_identifier = 'TEST_TS_SMALL';
alter system dump datafile 1969 block min 2 block max 3;
alter session set tracefile_identifier = 'TEST_TS_12G_BFT_FALSE';
alter system dump datafile 1970 block min 2 block max 3;
--============ TEST_TS ============--
Start dump data blocks tsn: 2882 file#:1968 minblk 2 maxblk 3
BH (0x118fd2048) file#: 1968 rdba: 0x00000002 (1024/2) class: 13 ba: 0x118bbe000
frmt: 0x02 chkval: 0xff66 type: 0x4c=KTFBN File Space Header
File Space Header Block:
------------------------
Header Control:
---------------
RelFno:1024 Unit:8 Size:128000 Initial:8691 Tail:128000
AutoExtend:NO Increment:0 MaxSize:0
Flag:0x00000901 (BASIC/-/-/-/-/-/-/-/BIGF/-/AUTOALLOC)
Deallocation scn: 0x00000000fb5cd8ea
BigFile Header Control:
-----------------------
First_FSB:3 FSB_Batch:128 FSB_Chunk:1 FSB_FFBs:16384
First_FFB:131 FFB_Chunk:16 FFB_AUs:32768
-----------------
FSB instance map:
-----------------
[ 0] 0x00000003 (3)
METADATA Table:
---------------
[ 0] flag:0x7(I/S/P) first:0x00000093(147) len:96
BH (0x128fd8df8) file#: 1968 rdba: 0x00000003 (1024/3) class: 12 ba: 0x128c5a000
frmt: 0x02 chkval: 0xa038 type: 0x4d=KTFBN File Space Level 2 Bitmap
FSB BitMap Control:
-------------------
begin:0 unit:16384 flag:0x1
FFB Bitmap:
--============ TEST_TS_SMALL ============--
Start dump data blocks tsn: 2883 file#:1969 minblk 2 maxblk 3
BH (0x154f7dbb0) file#: 1969 rdba: 0xec800002 (946/2) class: 13 ba: 0x154440000
frmt: 0x02 chkval: 0xbc13 type: 0x1d=KTFB Bitmapped File Space Header
File Space Header Block:
Header Control:
RelFno: 946, Unit: 8, Size: 128000, Flag: 1(0x1)
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 126, Tail: 127999, First: 368, Free: 15616
BH (0x124fd52e8) file#: 1969 rdba: 0xec800003 (946/3) class: 12 ba: 0x124c06000
frmt: 0x02 chkval: 0xac2b type: 0x1e=KTFB Bitmapped File Space Bitmap
File Space Bitmap Block:
BitMap Control:
RelFno: 946, BeginBlock: 128, Flag: 0, First: 368, Free: 63120
--============ TEST_TS_12G_BFT_FALSE ============--
Start dump data blocks tsn: 2884 file#:1970 minblk 2 maxblk 3
BH (0x162fc1248) file#: 1970 rdba: 0x00000002 (1024/2) class: 13 ba: 0x162a3e000
frmt: 0x02 chkval: 0xdbd1 type: 0x1d=KTFB Bitmapped File Space Header
File Space Header Block:
Header Control:
RelFno: 1024, Unit: 8, Size: 128000, Flag: 1(0x1)
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 126, Tail: 127999, First: 368, Free: 15616
BH (0x118fb5fb0) file#: 1970 rdba: 0x00000003 (1024/3) class: 12 ba: 0x118940000
frmt: 0x02 chkval: 0x4719 type: 0x1e=KTFB Bitmapped File Space Bitmap
File Space Bitmap Block:
BitMap Control:
RelFno: 1024, BeginBlock: 128, Flag: 0, First: 368, Free: 63120
In TEST_TS dump, block 2 and 3 types are prefixed with "KTFBN", which are:
KTFBN File Space Header KTFBN File Space Level 2 Bitmapwhereas in TEST_TS_SMALL dump, block 2 and 3 types are prefixed with "KTFB", which are:
KTFB Bitmapped File Space Header KTFB Bitmapped File Space BitmapTEST_TS_12G_BFT_FALSE is a BIGFILE tablespace similar to TEST_TS, but with "_enable_12g_bft"=FALSE (default is TRUE). Then its header and bitmap are same as SMALLFILE.
The extents inside a datafile of a locally managed tablespace are tracked in KTFBN or KTFB Bitmap blocks. It seems that this special 12g hidden parameter "_enable_12g_bft" switched Bitmap type identification from KTFB to KTFBN.
About KTFB Bitmap blocks, Blog: Deciphering extent management internals inside locally managed tablespaces has a deep investigation.
3. Segment Header and Bitmap Dump
For 3 created tables, we first find their HEADER_BLOCK, and then dump HEADER_BLOCK and its two preceding blocks:
SQL> select segment_name, header_file, header_block from dba_segments where segment_name in ('TEST_TAB', 'TEST_TAB_SMALL', 'TEST_TAB_12G_BFT_FALSE');
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
---------------------- ------- ----------- ------------
TEST_TAB 1968 8698
TEST_TAB_SMALL 1969 130
TEST_TAB_12G_BFT_FALSE 1970 130
alter session set tracefile_identifier = 'TEST_TAB';
alter system dump datafile 1968 block min 8696 block max 8698;
alter session set tracefile_identifier = 'TEST_TAB_SMALL';
alter system dump datafile 1969 block min 128 block max 130;
alter session set tracefile_identifier = 'TEST_TAB_12G_BFT_FALSE';
alter system dump datafile 1970 block min 128 block max 130;
--============ TEST_TAB ============--
Start dump data blocks tsn: 2882 file#:1968 minblk 8696 maxblk 8698
BH (0x118f9aa58) file#: 1968 rdba: 0x000021f8 (1024/8696) class: 8 ba: 0x1186d2000
frmt: 0x02 chkval: 0xe677 type: 0x20=FIRST LEVEL BITMAP BLOCK
Dump of First Level Bitmap Block
--------------------------------
First free datablock : 16
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x000021f8 Length: 8 Offset: 0
BH (0xfbfa0890) file#: 1968 rdba: 0x000021f9 (1024/8697) class: 9 ba: 0xfb758000
frmt: 0x02 chkval: 0x74b9 type: 0x21=SECOND LEVEL BITMAP BLOCK
Dump of Second Level Bitmap Block
number: 52 nfree: 1 ffree: 51 pdba: 0x000021fa
BH (0xfaf8d8d0) file#: 1968 rdba: 0x000021fa (1024/8698) class: 4 ba: 0xfa5a8000
frmt: 0x02 chkval: 0x7293 type: 0x23=PAGETABLE SEGMENT HEADER
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 38 #blocks: 2944
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x00002d60 ext#: 37 blk#: 96 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 2912
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x00002d60 ext#: 37 blk#: 96 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 2912
mapblk 0x00000000 offset: 37
Level 1 BMB for High HWM block: 0x00002d01
Level 1 BMB for Low HWM block: 0x00002d01
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
Map Header:: next 0x00000000 #extents: 38 obj#: 3998654 flag: 0x10000000
Extent Map
-----------------------------------------------------------------
0x000021f8 length: 8
0x00002280 length: 128
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x000021f8 Data dba: 0x000021fb
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x000021f9
The dump of TEST_TAB_SMALL and TEST_TAB_12G_BFT_FALSE are very similar to TEST_TAB.
