Mechanism to Recycle Database Constraint Identifiers (Doc ID 2925056.1)

Last updated on APRIL 17, 2023

Applies to:

Goal

In the Oracle data dictionary, certain identifiers are kept with internal sequences. Since these identifiers are used in the code in many areas, they are kept in variables with limits to their size. See Document 2660231.1 for an overview of these identifiers.

One such identifier is related to uniquely identifying object constraints. Although the maximum number of constraints that may be imposed on a table is theoretically unlimited, there is a cap on the maximum number of constraints that may be created in the database: 4,294,967,294. If this limit is exceeded, any further attempt to create a constraint will fail with ORA-600 [12807]. To ascertain how close to this limit a database is, the following query can be run:

When this result reaches '0', no more constraints may be created and attempts to create such constraints will receive ORA-600 [12807].

In the default configuration, for databases less than version 23c the con# identifiers always increase and are never recycled, even if the constraints that they correspond to have been dropped.

This document outlines a solution to this problem and how to implement it.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.