Datafile Auto Extend feature behavior with OMF & non-OMF files system.

For normal file system (non-omf), the default AUTOEXTEND OFF and if change to ON, NEXT INITIAL SIZE is 1 Database block (based on the blocksize of the tablespace).

SQL> select AUTOEXTENSIBLE,INCREMENT_BY from dba_data_files where file_id=5;

AUT INCREMENT_BY

--- ------------

NO             0

SQL> ALTER DATABASE DATAFILE 5 AUTOEXTEND ON;

Database altered.

SQL> select AUTOEXTENSIBLE,INCREMENT_BY from dba_data_files where file_id=5;

AUT INCREMENT_BY

--- ------------

YES            1

Why is the default 1 Database block is not good for most of the system ? Because when the at any time datafile is full, Oracle will extend it 1 block at-a-time, making a call to the OS to add 1 block on each occasion. Obviously, even if you are extending a table or index with an Extent of 64KB, Oracle has to make 8 calls to the OS (with a datafile block size of 8KB). That is where you will see "data file init write" waits.

But when you use OMF (Oracle managed file system) & Oracle defaults initial size is 100MB instead of 1 and also AUTOEXTEND is on instead of off which is by default for non-omf filesystem.

SQL> alter system set db_create_file_dest='C:\APP\JAYVA\ORADATA\TARGET';

System altered.

SQL> create tablespace test_omf;

Tablespace created.

SQL> select file_name from dba_data_files where tablespace_name='TEST_OMF';

FILE_NAME

--------------------------------------------------------------------------------

C:\APP\JAYVA\ORADATA\TARGET\TARGET\DATAFILE\O1_MF_TEST_OMF_7OD768GW_.DBF

SQL> select file_id,AUTOEXTENSIBLE,INCREMENT_BY from dba_data_files where tablespace_name='TEST_OMF';

   FILE_ID AUT INCREMENT_BY

---------- --- ------------

         6 YES        12800