When you try to add data files or resize existing data files, you cannot go with any number in your mind. Oracle database has limitations which you must consider before you arrive at a conclusion.
Below are the important limitations that you must be aware of. Each limitation has its on corresponding dependencies.
Oracle physical db limitations
Here I will be discussing about Oracle 11g and 12c physical limitations:
DATABASE BLOCK SIZE:
min 2KB and max dependent on OS but never exceeds 32K
min in the initial extent of a segment = 2 blocks
max per data file are platform dependent = 222 – 1 blocks
max per tablespace = OS dependent, usually 1022
max per database = 65533, sometimes less on different OS
DATABASE FILE SIZE:
max size OS dependent. Limited by maximum operating system file size; typically 222 or
4MB blocks. The max size of a single datafile is limited by the database block size. Generally it’s 32TB for 8K block and 128TB for 32K block.
max no of tablespaces per database = 64K.
Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file.
min 1 controlfile required, recommended to have more than 1
max size = 25000 X (db block size)
min size = 4MB
max size = OS dependent, typically 2 GB
max number of logfiles = limited by MAXLOGFILES parameter
max number of logfiles per group = unlimited