Oracle9.2, RH Linux As we found out or suspected in other databases on Linux, autoextensible datafiles do not autoextend: SQL> select * from dba_tablespaces where tablespace_name = 'FI_STAGE_DATA'; TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN ------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- ALLOCATIO PLU SEGMEN --------- --- ------ FI_STAGE_DATA 16384 524288000 524288000 1 2147483645 0 524288000 ONLINE PERMANENT LOGGING NO LOCAL UNIFORM NO AUTO SQL> select * from dba_data_files where tablespace_name = 'FI_STAGE_DATA'; FILE_NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- /tunocw/oradata/data01/fi_stage_data01.dbf 17 FI_STAGE_DATA 4718592000 288000 AVAILABLE 17 YES 5033164800 307200 32000 4194304000 256000 The above SQL says AUTOEXTENSIBLE = YES. SQL> select * from dba_free_space where tablespace_name = 'FI_STAGE_DATA'; no rows selected But I cannot create a table even using a small extent size. SQL> create table yongtest (a number) storage (initial 1k next 1k) tablespace fi_stage_data; create table yongtest (a number) storage (initial 1k next 1k) tablespace fi_stage_data * ERROR at line 1: ORA-01658: unable to create INITIAL extent for segment in tablespace FI_STAGE_DATA So I have to extend the file a little. SQL> alter database datafile '/tunocw/oradata/data01/fi_stage_data01.dbf' resize 4718657536; alter database datafile '/tunocw/oradata/data01/fi_stage_data01.dbf' resize 4718657536 * ERROR at line 1: ORA-02490: missing required file size in RESIZE clause SQL> alter database datafile '/tunocw/oradata/data01/fi_stage_data01.dbf' resize 4608064k; Database altered. Not sure why I need to use 4608064k instead of the equivalent 4718657536. Now I see free space 500m! SQL> select * from dba_free_space where tablespace_name = 'FI_STAGE_DATA'; TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ------------------------------ ---------- ---------- ---------- ---------- ------------ FI_STAGE_DATA 17 256005 524288000 32000 17 The reason just extending by 64k (4718657536 - 4718592000 = 65536) gives me 500m is because you have to leave 64k file header to accomodate the extent bitmap in LMT. If not, you could waste the whole extent-sized chunk at the end of the file.