A.3 Logical Database Limits

This table describes limit types and limit values for logical database items.

Item Type of Limit Limit Value

Columns

Maximum per table

If MAX_COLUMNS = STANDARD: 1000

If MAX_COLUMNS = EXTENDED: 4096 (available starting with Oracle Database 23ai)

See Also: "MAX_COLUMNS" initialization parameter for additional details

Columns

Maximum per index (or clustered index)

32

Columns

Maximum per bitmapped index

30

Constraints

Maximum per column

Not defined

Oracle does not define a limit on the number of constraints per column. However, constraints are subject to the limit on the maximum number of constraints in the database. See the entry for "Constraints - Maximum per database" in this table.

Constraints

Maximum per database

4,294,967,293

Constraints are numbered internally from 1 to 4,294,967,294. The internal number is incremented each time a constraint is created. One number is always consumed by a special internal user called _NEXT_CONSTRAINT, which represents the next available number for a new constraint. Starting with Oracle Database 23ai, the database reuses constraint numbers. Therefore, the maximum number of constraints that can exist in the database at any given time is 4,294,967,293.

You can view constraint numbers by querying the CON# column of the SYS.CON$ table.

Dictionary-managed database objects

Maximum per database

4,254,950,911 - overhead

  • The value of 4,254,950,911 is derived as follows: The data type used for object identifiers can store a maximum value of 4,294,967,294. However, approximately 40 million of these values are reserved at the high end for other purposes, which results in a maximum object identifier value of 4,254,950,911.

  • Overhead includes the following:

    • Multiple object identifiers for one database object

      Database objects consume one object identifier at the time of creation. This identifier is displayed in both the OBJECT_ID and DATA_OBJECT_ID columns of the ALL_OBJECTS view. Over the lifetime of an object, the value of OBJECT_ID remains unchanged, but the value of DATA_OBJECT_ID changes when the object is altered, for example, with a DDL statement or a table truncation. Therefore, one object can consume more than one object identifier over its lifetime.

    • Object identifiers for lightweight jobs

      Lightweight jobs consume object identifiers, even though they are not permanent objects. Therefore, if many lightweight jobs are running in the database, the number of objects that can be created in the database is dependent upon the remaining pool of identifiers.

Indexes

Maximum per table

Not defined

Oracle does not define a limit on the number of indexes per table. However, indexes are subject to the limit on the maximum number of dictionary-managed database objects allowed per database. See the entry for "Dictionary-managed database objects" in this table.

Indexes

Total size of indexed column(s)

Approximately 75% of the database block size minus some overhead

Partitions

Maximum length of linear partitioning key

4 KB - overhead

Partitions

Maximum number of columns in partition key

16 columns

Partitions

Maximum number of partitions allowed per table or index

1024K - 1

Rows

Maximum number per table

Not defined

Stored Packages

Maximum size

Approximately 6,000,000 lines of code.

See Also: Oracle Database PL/SQL Language Reference for details

Subpartitions

Maximum number of subpartitions in a composite partitioned table

1024K - 1

Subqueries

Maximum levels of subqueries in a SQL statement

255 subqueries in the WHERE clause

A limit is not defined for subqueries in the FROM clause of the top-level query.

System Change Numbers (SCNs)

Maximum

263 - 248 = 9,223,090,561,878,065,152 SCNs

Tables

Maximum per clustered table

32 tables

Tables

Maximum per database

Not defined

Oracle does not define a limit on the number of tables per database. However, tables are subject to the limit on the maximum number of dictionary-managed database objects allowed per database. See the entry for "Dictionary-managed database objects" in this table.

Users and Roles

Maximum

2,147,483,638

Users and roles are numbered internally from 0 to 2,147,483,638. The internal number is incremented each time a user or role is created, and numbers are never recycled. One number is always consumed by a special internal user called _NEXT_USER, which represents the next available number for a new user or role. Therefore, the maximum number of users and roles that can be created during the lifetime of a database is 2,147,483,638.

You can view user and role numbers by querying the USER# column of the SYS.USER$ table.

Note:

The limit on how long a SQL statement can be depends on many factors, including database configuration, disk space, and memory

Note:

When an object instance exists in memory, there is no fixed limit on the number of attributes in the object. But the maximum total amount of memory consumed by an object instance is 4 GB. When an object instance is inserted into a table, the attributes are exploded into separate columns in the table, and these columns count toward the limit on the maximum number of columns per table.