Oracle 23c New Features for DBA'sVery old thing, but to just refresh, Ever wonder how the extent sizes is managed if your tablespace is in LMT and autoallocate as like below.
CREATE TABLESPACE large_tables
DATAFILE ‘/u01/oracle/data/DB1/large_tables_01.dbf’
SIZE 100M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
When you specify auto allocate, Oracle will allocate extent sizes of 64KB, 1MB, 8MB, 32MB or 64MB. and even 256MB (according to Jonathan in his very old post)
The first sixteen extents will all be 64KB in size.
When the seventeenth extent is allocated, Oracle decides that it had the wrong extent size and allocates a larger, 1MB extent.
After sixteen 1MB extents have been allocated, future extents will be 8MB in size.
In short, (but this depends on the space available too)
IF dba_segment.bytes < 1M THEN
next extent = 64K
ElSIF 1M <= dba_segment.bytes < 64M THEN
next extent = 1M
ELSIF 64M <= dba_segment.bytes < 1G
THEN next extent = 8M
ELSE
next extent = 64MB,
Further , Each BMB controls up to:
=> 16 blocks until the segment is 1Mb
=> 64 blocks until the segment is 32Mb
=> 256 blocks until the segment is 1Gb
=> 1024 blocks once segment is >1Gb
Very good article on LMT vs DMT: Download here
Follow Me!!!