Skip to content

oracle file size limits and other limits – new oracle dba reference





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

Parameter Default Maximum Value
MAXLOGFILES 16 255
MAXLOGMEMBERS 2 5
MAXLOGHISTORY 100 65534
MAXDATAFILES 30 65534
MAXINSTANCES 1 63

 

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

Table H-2 File Size Limits

File Type Platform  
Data files Any 4,194,303 multiplied by the value of the DB_BLOCK_SIZE parameter
Import/Export files and SQL*Loader files Tru64 UNIX 16 TB
  AIX, HP-UX, Linux, and Solaris: 32-bit with 32-bit files 2,147,483,647 bytes
  AIX, HP-UX, Linux, Mac OS X, and Solaris: 64-bit files Unlimited
Control files HP-UX, Linux, Mac OS X, and Solaris 20000 database blocks
  AIX 10000 database blocks
  Tru64 UNIX 19200 database blocks

 

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.

What is The Maximum Datafile Size Limit In Oracle Database 10gR2 [ID 804733.1]  

 
  Modified 28-SEP-2009     Type HOWTO     Status PUBLISHED  
       

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:

Database Block Size Maximum Datafile File Size
2k 4194303 * 2k    = 8 GB
4k 4194303 * 4k    = 16 GB
8k 4194303 * 8k    = 32 GB
16k 4194303 * 16k  = 64 GB
32k 4194303 * 32k  = 128 GB

 

Max datafile size for BIG FILE TABLESPACE would be:

Database Block Size Maximum Datafile Size
2k 4294967295 * 2k   = 8 TB
4k 4294967295 * 4k   = 16 TB
8k 4294967295 * 8k   = 32 TB
16k 4294967295 * 16k = 64 TB
32k 4294967295 * 32k = 128 TB

 

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.

VN:F [1.9.22_1171]
Rating: 4.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
oracle file size limits and other limits - new oracle dba reference, 4.0 out of 10 based on 1 rating

Post a Comment

You must be logged in to post a comment.