+++++++++++++++++++++++++++++++ Minimum datafile size for locally managed tablespace with uniform extent size is 1 extent + 2 blocks + 1 byte. E.g. (suppose db_block_size = 8192): +++++++++++++++++++++++++++++++ SQL> create tablespace tmp datafile '/tmp/tmp01.dbf' size 1064960 extent management local uniform size 1048576; create tablespace tmp datafile '/tmp/tmp01.dbf' size 1064960 extent management local uniform size 1048576 * ERROR at line 1: ORA-03214: File Size specified is smaller than minimum required SQL> create tablespace tmp datafile '/tmp/tmp01.dbf' size 1064961 extent management local uniform size 1048576; Tablespace created. 1048576 = 8192*128 1064960 = 1048576+2*8192 SQL> drop tablespace tmp including contents and datafiles; Tablespace dropped. +++++++++++++++++++++++++++++++ Test with extent size 128k (131072): +++++++++++++++++++++++++++++++ SQL> create tablespace tmp datafile '/tmp/tmp01.dbf' size 147456 extent management local uniform size 131072; create tablespace tmp datafile '/tmp/tmp01.dbf' size 147456 extent management local uniform size 131072 * ERROR at line 1: ORA-03214: File Size specified is smaller than minimum required SQL> create tablespace tmp datafile '/tmp/tmp01.dbf' size 147457 extent management local uniform size 131072; Tablespace created. SQL> select * from dba_free_space where tablespace_name = 'TMP'; TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS ------------------------------ ---------- ---------- ---------- ---------- RELATIVE_FNO ------------ TMP 8 4 131072 16 8 SQL> !ls -l /tmp/tmp01.dbf -rw-r----- 1 oracle dba 163840 Feb 4 10:18 /tmp/tmp01.dbf +++++++++++++++++++++++++++++++ The OS level filesize is exactly 1 extent + 4 blocks (i.e. 163840 = 131072+4*8192). +++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++ When there's a segment in the tablespace, the datafile has to have extra 7 blocks + 1 byte (57345 bytes) to tightly compact data: +++++++++++++++++++++++++++++++ 131072 = 8192*16 450560 = 131072*3+8192*7 SQL> create tablespace tmp datafile '/tmp/tmp01.dbf' size 450560 extent management local uniform size 131072; Tablespace created. SQL> create table tmp (a number) tablespace tmp; Table created. SQL> alter table tmp allocate extent; Table altered. SQL> alter table tmp allocate extent; alter table tmp allocate extent * ERROR at line 1: ORA-01653: unable to extend table YONG.TMP by 16 in tablespace TMP SQL> select * from dba_free_space where tablespace_name = 'TMP'; no rows selected SQL> alter database datafile '/tmp/tmp01.dbf' resize 450561; Database altered. SQL> select * from dba_free_space where tablespace_name = 'TMP'; TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ------------------------------ ---------- ---------- ---------- ---------- ------------ TMP 8 41 131072 16 8 SQL> alter table tmp allocate extent; Table altered. +++++++++++++++++++++++++++++++ But if db_block_size is 16384, the tablespace needs space for the segment(s) plus 3 blocks + 1 byte (49153 bytes). So why 7 blocks + 1 byte for 8192 block size and 3 blocks + 1 byte for 16384? Because the datafile has to have 64k-1block+1 datafile header, i.e. 64*1024-8192+1=57345 and 64*1024-16384+1=49153 bytes, respectively. And this overhead doesn't count the OS level file overhead, which is another db_block_size. Guideline to maximize datafile space usage: if db_block_size=8192, create tablespace datafile size integer*extentsize+49153; if db_block_size=16384, create tablespace datafile size integer*extentsize+57345; +++++++++++++++++++++++++++++++ SQL> create tablespace tmp datafile '/tmp/tmp01.dbf' size 393216 reuse extent management local uniform size 131072; Tablespace created. SQL> create table tmp (a number) tablespace tmp; Table created. SQL> alter table tmp allocate extent; Table altered. SQL> alter table tmp allocate extent; alter table tmp allocate extent * ERROR at line 1: ORA-01653: unable to extend table SYSTEM.TMP by 8 in tablespace TMP SQL> select * from dba_free_space where tablespace_name = 'TMP'; no rows selected SQL> alter database datafile '/tmp/tmp01.dbf' resize 442368; Database altered. SQL> select * from dba_free_space where tablespace_name = 'TMP'; no rows selected SQL> alter database datafile '/tmp/tmp01.dbf' resize 442369; Database altered. SQL> select * from dba_free_space where tablespace_name = 'TMP'; TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ------------------------------ ---------- ---------- ---------- ---------- ------------ TMP 15 21 131072 8 15 SQL> alter table tmp allocate extent; Table altered.