Maximum Oracle Limits

Oracle Database Tips by Donald BurlesonFebruary 16, 2015

Question: I have a job interview and I am expected to memorize the maximum limits for Oracle tables, indexes files ad tablespace. Why is it important to know the maximum values for these items?

Answer: I have been a DBA for more than 25 years and I have never hit the huge upper limits for Oracle tables, and the maximum values for tablespace and data files are nothing more than idle exercise.

However, here are the maximum values for some of the most important Oracle structures:

Item	Maximum Limit
Maximum advanced queuing processes per instance	10
Maximum background process slave processes	15
Maximum blocks per file	2²² - 1 blocks
Maximum columns per bitmap index	30 columns maximum
Maximum columns per b-tree index	32 columns
Maximum columns per table	1,000
Maximum database block size	32k except for Windows at 16k
Maximum files per database	65,533 files, except for Windows
Maximum files per tablespace	1,022 files
Maximum global cache service processes	10
Maximum index column size	75% of the database block size less minor overhead
Maximum job queue processes per instance	1,000
Maximum length of linear partitioning key	4 KB - overhead
Maximum number of columns in partition key	16 columns
Maximum number of partitions per table/ index	1,024,000 - 1
Maximum number of roles	2,147,483,638
Maximum number of users	2,147,483,638
Maximum sessions per instance	32k sessions
Maximum table/index extents	(2GB * block size)
Maximum tables per cluster	32 tables
Maximum tablespaces per database	64k tablespaces

��