建立cache group 時遇到 錯誤5120 時候的解決方案

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