创建cache group 时遇到错误5120 时候的解决方案

最新推荐文章于 2023-05-07 12:06:40 发布

Ellison

最新推荐文章于 2023-05-07 12:06:40 发布

阅读量1.3k

点赞数

分类专栏： TimesTen

TimesTen 专栏收录该内容

8 篇文章

订阅专栏

本文深入探讨了Oracle数据库中出现5120错误的原因，详细介绍了通过查询找到表对应索引状态的方法，并提供了使约束生效的步骤。解释了在创建缓存组时，如果表的主键、唯一性或非空约束设为NOVALIDATE状态，将导致创建失败。同时提供了调整约束状态以避免此类错误发生的指导。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

5120 错误大致是由于oracle 上的表主键或其它关键约束处于NOVALIDATE 状态导致的。

所以你可以通过如下查询找到表所对应索引的状态：

SQL> select constraint_name, constraint_type, validated, status from
        all_constraints where table_name = 'MyTable';

CONSTRAINT_NAME                C VALIDATED     STATUS
------------------------------ - ------------- --------
REFID_CONSTRAINT               C VALIDATED     ENABLED
PKEY_CONSTRAINT                P NOT VALIDATED DISABLED

然后使之生效：

SQL> alter table MyTable modify constraint PKEY_CONSTRAINT validate;

--------------官方文档中是这样描述的：

Declaring NOVALIDATE on constraints causes cache group creation failure

If the Oracle database table on which you want to create the cache group declares NOVALIDATE on columns with primary key, UNIQUE or NOT NULL constraints, the creation of the cache group fails.

Note:
This does not apply to any foreign key constraints. However, TimesTen recommends that any matching foreign key is in the enabled VALIDATE state. Your workload performance may be affected when you alter a foreign key column to the enabled VALIDATE state.

TimesTen perceives a NOVALIDATE on a primary key or NOT NULL table column definition as a NULL and, therefore, not qualified as a column on which to build the cache group. Thus, all columns with the primary key, UNIQUE and NOT NULL column constraints must be enabled with the VALIDATE state when creating a cache group from the Oracle database table.

When you create a cache group from an Oracle database table with one or more of these constraints, the following errors are thrown:

5124: Autorefresh/propagate are not allowed on restricted cache group
5168: Restricted cache groups are deprecated
5120: No matching unique index with not null columns, unique key constraint
with not null columns, or primary key constraint on table EVENTLOG, cache
operations are restricted.

If you receive these errors, you can perform a SELECT statement to verify any existing NOVALIDATE constraints on the Oracle database table. The following SELECT statement shows all constraints on the MyTable table:

SQL> select constraint_name, constraint_type, validated, status from
        all_constraints where table_name = 'MyTable';

CONSTRAINT_NAME                C VALIDATED     STATUS
------------------------------ - ------------- --------
REFID_CONSTRAINT               C VALIDATED     ENABLED
PKEY_CONSTRAINT                P NOT VALIDATED DISABLED

If the table column that is to be the primary key for the cache table is enabled as NOVALIDATE, perform the following steps to enable the column with the VALIDATE state:

    Enable the NOVALIDATE state for the primary key column.

    SQL> alter table MyTable modify constraint PKEY_CONSTRAINT
               enable novalidate;
    Table altered.

    SQL> select constraint_name, constraint_type, validated, status
              from all_constraints where table_name = 'MyTable';

    CONSTRAINT_NAME                C VALIDATED     STATUS
    ------------------------------ - ------------- --------
    REFID_CONSTRAINT               C VALIDATED     ENABLED
    PKEY_CONSTRAINT                P NOT VALIDATED ENABLED

    Enable the VALIDATE state for the primary key column.

    SQL> alter table MyTable modify constraint PKEY_CONSTRAINT validate;
    Table altered.

    SQL> select constraint_name, constraint_type, validated, status
              from all_constraints where table_name = 'MyTable';

    CONSTRAINT_NAME                C VALIDATED     STATUS
    ------------------------------ - ------------- --------
    REFID_CONSTRAINT               C VALIDATED     ENABLED
    PKEY_CONSTRAINT                P VALIDATED     ENABLED