Oracle Database 23ai Physical and Logical Limits

Oracle Database limits describe the boundaries within which the database engine, operating system, storage platform, and application architecture must operate. Some limits are hard architectural ceilings. Others are controlled by initialization parameters, database compatibility, the selected edition, or the service in which the database runs.
For that reason, a single static table cannot describe every Oracle Database 23ai installation accurately. A database administrator should distinguish between physical limits, logical limits, configurable limits, multitenant architecture limits, and edition-specific restrictions, then verify the settings of the installed database.

Oracle limits fall into several categories:

1. Physical limits govern datafiles, blocks, tablespaces, redo logs, and other storage structures.
2. Logical limits govern schema objects, identifiers, columns, partitions, constraints, and SQL structures.
3. Configurable limits are influenced by parameters such as DB_FILES, PROCESSES, SESSIONS, and MAX_STRING_SIZE.
4. Architectural limits depend on the CDB/PDB model, Oracle RAC, and the scope at which a resource is managed.
5. Edition and service limits are imposed by Oracle Database Free, licensed editions, Autonomous Database, or another managed service.

A configured value is not necessarily a hard engine maximum, and a documented maximum is not necessarily a recommended operating target.

Many physical storage limits remained broadly stable after Oracle Database 12c. The more meaningful changes involve architecture, identifier length, string-size configuration, and newer wide-table capabilities.
Oracle Database 12c introduced the multitenant architecture while still permitting non-CDB databases. Oracle Database 23ai uses the multitenant model, with a container database, the CDB root, PDB$SEED, and one or more pluggable databases.
Oracle Database 12.2 also expanded many identifier lengths from the historical 30-byte limit to as many as 128 bytes. This permits longer table, column, constraint, and other schema-object names, subject to compatibility and object-specific exceptions.

Oracle physical limits depend on block size, tablespace type, operating system, storage system, and initialization parameters.

The earlier practice of multiplying rounded values for block size, blocks per file, and file count to produce one theoretical maximum database size is misleading. It ignores smallfile versus bigfile tablespaces, platform limits, DB_FILES, service restrictions, and the multitenant architecture. Practical capacity planning should use the characteristics of the actual database and storage platform.

Use dynamic performance and data dictionary views to inspect the installed system:

SELECT value AS db_block_size_bytes
FROM v$parameter
WHERE name = 'db_block_size';

SELECT value AS configured_db_files
FROM v$parameter
WHERE name = 'db_files';

SELECT COUNT(*) AS current_datafile_count
FROM v$datafile;

SELECT tablespace_name,
       bigfile,
       extent_management,
       segment_space_management
FROM dba_tablespaces
ORDER BY tablespace_name;

SELECT tablespace_name,
       file_name,
       bytes,
       maxbytes,
       autoextensible
FROM dba_data_files
ORDER BY tablespace_name,
         file_name;

Access to DBA and dynamic performance views requires suitable privileges.

The historical statement that VARCHAR2 is limited to 4,000 characters is incomplete. Oracle usually expresses these limits in bytes, and character semantics can differ in a multibyte character set.
With MAX_STRING_SIZE=STANDARD, SQL VARCHAR2 values are generally limited to 4,000 bytes and SQL RAW values to 2,000 bytes. With MAX_STRING_SIZE=EXTENDED, VARCHAR2, NVARCHAR2, and RAW can support values as large as 32,767 bytes, subject to datatype and character-set rules.
Check the configured value:

SELECT value
FROM v$parameter
WHERE name = 'max_string_size';

Changing MAX_STRING_SIZE is an administrative migration rather than a normal dynamic parameter change. Large character values that exceed ordinary SQL string requirements should normally use CLOB.

Oracle Database 12.2 and later permit many identifiers as long as 128 bytes. This applies to common schema objects such as tables, columns, indexes, and constraints, although individual object types can have exceptions.
Longer identifiers improve clarity, but excessively long names can reduce readability in SQL, reports, and administration tools. Naming standards should balance descriptiveness with operational convenience.

Traditional Oracle configurations support as many as 1,000 columns per table or view. Newer Oracle AI Database releases can support as many as 4,096 columns when MAX_COLUMNS=EXTENDED is available and the documented compatibility and client requirements are met.
Do not assume that every Oracle Database 23ai installation exposes this parameter. Verify the installed version and recognized parameters:

SELECT banner_full
FROM v$version;

SELECT name,
       value,
       isdefault
FROM v$parameter
WHERE name IN
(
    'compatible',
    'max_columns',
    'max_string_size'
)
ORDER BY name;

If MAX_COLUMNS is absent, the installed release does not expose it. Even when 4,096 columns are technically supported, very wide tables can increase row chaining, client compatibility issues, query complexity, and maintenance cost. A larger numeric limit does not replace sound relational design.

Concurrent-session capacity is not a universal 32K limit. It depends on PROCESSES, SESSIONS, memory, operating-system limits, server architecture, connection pooling, Oracle RAC, and service restrictions.
Inspect the configured values:

SELECT name,
       value
FROM v$parameter
WHERE name IN ('processes', 'sessions')
ORDER BY name;

Inspect current and peak use:

SELECT resource_name,
       current_utilization,
       max_utilization,
       limit_value
FROM v$resource_limit
WHERE resource_name IN
(
    'processes',
    'sessions'
)
ORDER BY resource_name;

A higher configured limit consumes resources and is not automatically an appropriate capacity target. Size these parameters according to workload, connection-pool behavior, and available memory.

Oracle Database 23ai operates through a multitenant container database. Some limits and parameters apply to the complete CDB, while others can be managed for individual PDBs. Oracle RAC can add another scope because multiple instances access the same database.
Identify whether the database is a CDB:

SELECT cdb
FROM v$database;

Identify the current container:

SELECT sys_context('USERENV', 'CON_NAME') AS container_name
FROM dual;

In SQL*Plus or SQLcl, the client command SHOW CON_NAME also reports the current container.

A consolidated query helps distinguish recognized parameters from assumptions based on another release:

SELECT name,
       value,
       isdefault,
       issys_modifiable,
       ispdb_modifiable
FROM v$parameter
WHERE name IN
(
    'compatible',
    'db_block_size',
    'db_files',
    'max_columns',
    'max_string_size',
    'processes',
    'sessions'
)
ORDER BY name;

A parameter that does not appear is not recognized by the installed database release. This is particularly important for version-sensitive features such as extended wide-table support.

Oracle documentation sometimes describes rows per table, tables per database, or constraints as unlimited. This normally means that no smaller documented logical ceiling applies. Practical boundaries still arise from storage, object identifiers, memory, performance, manageability, backup time, and edition or service limits.
Similarly, Oracle Database Free and managed cloud services can impose CPU, memory, storage, or PDB restrictions that are not universal engine limits. Always keep edition and service restrictions separate from the core architectural limit table.

Published limit tables are useful references, but the active database provides the configuration that actually governs an installation. Before planning a large schema, storage migration, or connection-capacity increase:

1. identify the exact release and release update;
2. check COMPATIBLE and relevant initialization parameters;
3. inspect current and peak resource utilization;
4. confirm the operating-system and storage limits;
5. identify whether the setting applies to the CDB, PDB, or instance;
6. review edition and cloud-service restrictions;
7. test client and driver compatibility for extended features.

The next lesson concludes the module.