This article can act as a reference to the new oracle dba to know the limits oracle and its underlying operating systems has on various limits such as data file size , max data files etc and contains brief discussion and a metalink article.

H Database Limits

This appendix describes database limits.

H.1 Database Limits

Table H-1 lists the default and maximum values for parameters in a CREATE DATABASE or CREATE CONTROLFILE statement.

Note:

Interdependencies between these parameters may affect permissible values.

Table H-1 CREATE CONTROLFILE and CREATE DATABASE Parameters

Table H-2 lists the Oracle Database file size limits in bytes.

Table H-2 File Size Limits

I do not remember the size range but with a bigfile tablespace you can
support at least 128T datafile.  That should take a while to backup
and recover.

Meanwhile a smallfile tablespace can have up to 1022 32G files (if
8k).
That should be enough space for most sites.

At 32k data blocks, a bigfile tablespace can contain a single 128
Terabyte datafile. Times 64,000 and you have your 8 exabytes. Note that
smaller block sizes result in smaller databases.

DB Versions:10g, 11g

OS & versions: Aix 6.1, Sun OS 5.9, Solaris 10

This is what Oracle 11g Documentation
http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/limits002.htm
says about the Maximum Data file size

Operating system dependent. Limited by maximum operating system file size;typically 2^22 or 4 MB blocks

I don’t understand what this 2^22 thing is.

In our AIX machine and ulimit command show

$ ulimit -a

time(seconds)        unlimited

file(blocks)         unlimited  <-------------------------------------------

data(kbytes)         unlimited

stack(kbytes)        4194304

memory(kbytes)       unlimited

coredump(blocks)     unlimited

nofiles(descriptors) unlimited

threads(per process) unlimited

processes(per user)  unlimited

So, this means, In AIX that both the OS and Oracle can create a data file of any Size. Right?

What about 10g, 11g DBs running on Sun OS 5.9 and Solaris 10 ? Is there any Limit on the data file size?

For tablespaces with smallfile datafile maximun datafile size is 32GB, where as bigfile datafile size can be in TB (I am not sure of exact size but I believe it can be very very big).

The maximum data file size is calculated by:

 Maximum datafile size = db_block_size * maximum number of blocks

Hi,

For small file you can check by creating the datafile with autoextend on and maxsize unlimited. It defaults to 32GB and 32TB for bigfile.

Hi you can find it below SQL.

SELECT round(4194303*value/(1024*1024*1024),2) MaxFileSizeGByte from v$parameter where name=’db_block_size’;

Still your expecting ans. for your query. Check Metalink document 804733.1

http://download.oracle.com/docs/cd/B19306_01/server.102/b15658/appg_db_lmts.htm#i92495

IN addition to all of the above – Oracle provides an OS specific Admin manual.

http://download.oracle.com/docs/cd/B19306_01/server.102/b15658/appg_db_lmts.htm#i92495 shows the OS-based limits for *nix machines (and has a lot of other useful info), http://www.oracle.com/pls/db102/portal.portal_db?selected=3#index-PLA points to the WIndows counterparts, and
http://www.oracle.com/pls/db102/portal.portal_db?selected=3#index-ADM get you awful close to the OpenVMS one.

In this Document
  Goal
  Solution
     Maximum number of Database blocks allowed in a single datafile in 10gR2 are as follows:
     Max datafile size for SMALL FILE NORMAL TABLESPACE would be:
     Max datafile size for BIG FILE TABLESPACE would be:
  References

Applies to:

Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 10.2.0.4
Information in this document applies to any platform.

Goal

What is the maximum datafile size in Oracle rdbms 10gR2?

There are two levels of restriction on the Oracle Datafiles:
1. By Oracle rdbms s/w.
2. By Operating System.

The maximum datafile size restriction will be decided by the one which is lower among the two mentioned above. This note talks about the file size limit imposed by Oracle rdbms s/w in 10gR2.

Solution

Oracle s/w has actual restriction on the number of database blocks which can be allocated in a single datafile. So Maximum files size limit from Oracle side depends on the Database Block Size (DB_BLOCK_SIZE ).

Maximum number of Database blocks allowed in a single datafile in 10gR2 are as follows:

Small File Tablespace (Normal Tablespace)       : 4194303    (2^22 -1)
Big File Tablespace   (New in 10gR2)            : 4294967295 (2^32 -1)

Max datafile size for SMALL FILE NORMAL TABLESPACE would be:

Max datafile size for BIG FILE TABLESPACE would be:

In Oracle Database 10g, a new type of tablespace is introduced, called a BIGFILE tablespace.

The BIGFILE syntax must be specified during the tablespace creation as such:
Create bigfile tablespace MY_TS

The limitation of a BIGFILE tablespace is that it can ONLY have a SINGLE file in a tablespace.
By creating a tablespace using the BIGFILE syntax, Oracle increases the maximum number of blocks in a datafile from the 4 Million blocks mentioned above to a maximum of 4 Billion.
Now final max datafile size will be determined by :

LOWER(Max file size limit of Oracle, Max file size limit of OS)
The OS file size limit typically depends on OS and filesystem.

Example WINDOWS 32 bit OS:
==========================
Maximum Database block size : 16,384 bytes or 16 kilobytes (KB)

Maximum OS file size for a FAT file    : 4 GB
Maximum OS file size for a NTFS file   : 16 Exabytes (EB)

So maximum database file size with 16k DB_BLOCK_SIZE on WINDOWS 32 bit OS
on FAT filesystem is     : LOWER (64 GB, 4 GB)   = 4GB
on NTFS filesystem is    : LOWER (64 GB, 16 EB)  = 64GB

Please refer Oracle® Database Platform Guide 10g Release 2 (10.2) for Microsoft Windows (32-Bit)
for more details.

The OS Max file size limit should be confirmed by the OS vendor.