Always Free ATP
Importing dump, just for example the subpartition below
. . imported "OPAS60PROD"."OPAS_OT_DBG_SEG_SIZES":"PART_360"."SSP_360_1641" 484.6 KB 21121 rows
Takes
select owner,segment_name, partition_name, segment_type, tablespace_name, bytes, blocks, extents
from dba_segments where segment_name='OPAS_OT_DBG_SEG_SIZES' and partition_name='SSP_360_1641';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES BLOCKS EXTENTS
------------- ---------------------- --------------- ------------------ ----------------- ---------- ---------- ----------
OPAS60PROD OPAS_OT_DBG_SEG_SIZES SSP_360_1641 TABLE SUBPARTITION DATA 8388608 1024 1
dbms_space.SPACE_USAGE and DBMS_SPACE.UNUSED_SPACE show
Unformatted : 0 / 0
Freespace 1 ( 0 - 25% free) : 0 / 0
Freespace 2 ( 25 - 50% free) : 0 / 0
Freespace 3 ( 50 - 75% free) : 0 / 0
Freespace 4 ( 75 - 100% free) : 0 / 0
Full : 73 / 598,016
total_blocks/total_bytes : 1,024 / 8,388,608
unused_blocks/unused_bytes : 933 / 7,643,136
last_used_extent_file_id/last_used_extent_block_id : 9,066 / 379,520
last_used_block : 91
Question: why Oracle uses so much extra space for the subpartition?
Other observations:
- Also is reproducible on my local 19.3 Windows and Linux databases.
- Moving to UNIFORM 128K tablespace changes nothing regarding space usage.
alter table OPAS_OT_DBG_SEG_SIZES modify subpartition SSP_360_1641 deallocate unused;
changes nothing having 64 extents and almost all of them are empty.