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
|
222 – 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
|
