There is always some ambiguity regarding the datafile size limit on a given platform and version of the database. Here is a simple way of identifying the maximum size to which a datafile can grow before switching to the next datafile.

Suppose you have a locally managed tablespace 'NOL' with AUTOEXTEND on without the MAXEXTEND value specified (Note: Don't do this ... the reason to read this tip).

sqlplus sys/....

CREATE TABLESPACE nol
DATAFILE 'E:OradataARK1NolARK1_nol1.dbf' SIZE 5M REUSE
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
NOLOGGING
PERMANENT
ONLINE;

Now let's get the file id of the datafile created for the NOL tablespace

select file_id,file_name,autoextensible
from dba_data_files
where tablespace_name like 'NOL';

FILE_ID FILE_NAME AUT
---------- ----------------------------------- ---
9 E:ORADATAARK1NOLARK1_NOL1.DBF YES

Querying the filext$ table, we can get the value of the maximum size in database blocks to which the datafile can grow before switching to the next file. Technically this must be the maximum filesize that Oracle can understand for a database datafile.

select * from filext$ where file# = 9;

FILE# MAXEXTEND INC
---------- ---------- ----------
9 4194302 1

Getting the Blocksize of the Database:

select value from v$parameter where name = 'db_block_size';

VALUE
-----
4096

Setting the default would give you a maximum value of 4194302 blocks with increments of 1 block when the datafile extends. On a database of 4K block size this would work out to be:

select (4194302*4096)/1024 from dual;

(4194302*4096)/1024
-------------------
16'777'208

So, the database datafile can have a maximum size of 16.7 GByte on a 4k database block size and Windows 2000 platform. The above shows that the dependency is on the database block size for a given platform. Also if we do not set the MAXEXTEND for the datafile, then the first datafile would grow to the MAXEXTEND value and only then would shift to the next datafile for
a given tablespace with multiple datafiles. So it is imperative to set the MAXEXTEND value when turning on the AUTOEXTEND option.