Re: ORA-14098 - can anyone see what is wrong here

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 07 Jun 2007 19:58:35 +0200
Message-ID: <4668474B.1010008@gmail.com>

Your index XCHG_DLY_CPB_PK has to be local as well.

SQL> drop TABLE DLY_CPB ;
drop TABLE DLY_CPB

*

ERROR at line 1:
ORA-00942: table or view does not exist

SQL> CREATE TABLE DLY_CPB (

  2      ACCT_KEY                         NUMBER(10, 0)    NOT NULL,
  3      PROC_DT_VAL                      NUMBER(8, 0)     NOT NULL,
  4      EXT_STAT_CHNG_DT                 DATE

  5 )
  6 NOLOGGING
  7 PARTITION BY RANGE (PROC_DT_VAL)
  8 SUBPARTITION BY HASH (ACCT_KEY)
  9 SUBPARTITIONS 16
 10 (
 11 PARTITION P200703
 12 VALUES LESS THAN (20070401)
 13 NOLOGGING,
 14 PARTITION P200704
 15 VALUES LESS THAN (20070501)
 16 NOLOGGING
 17 )
 18 ;

Table created.

SQL> CREATE UNIQUE INDEX DLY_CPB_PK ON DLY_CPB(ACCT_KEY, PROC_DT_VAL)   2 NOLOGGING
  3 LOCAL
  4 ;

Index created.

SQL> ALTER TABLE DLY_CPB ADD

  2      CONSTRAINT DLY_CPB_PK PRIMARY KEY (ACCT_KEY, PROC_DT_VAL)
  3      USING INDEX

  4 ;

Table altered.

SQL> Drop TABLE XCHG_DLY_CPB ;
Drop TABLE XCHG_DLY_CPB

*

ERROR at line 1:
ORA-00942: table or view does not exist

SQL> CREATE TABLE XCHG_DLY_CPB (

  2      ACCT_KEY                         NUMBER(10, 0)    NOT NULL,
  3      PROC_DT_VAL                      NUMBER(8, 0)     NOT NULL,
  4      EXT_STAT_CHNG_DT                 DATE

  5 )
  6 NOLOGGING
  7 PARTITION BY HASH (ACCT_KEY)
  8 PARTITIONS 16
  9 ;

Table created.

SQL> CREATE UNIQUE INDEX XCHG_DLY_CPB_PK ON XCHG_DLY_CPB(ACCT_KEY, PROC_DT_VAL) LOCAL
  2 ;

Index created.

SQL> ALTER TABLE XCHG_DLY_CPB ADD

  2      CONSTRAINT XCHG_DLY_CPB_PK PRIMARY KEY (ACCT_KEY, PROC_DT_VAL)
  3      USING INDEX

  4 ;

Table altered.

SQL> alter table dly_cpb exchange partition p200704 with table xchg_dly_cpb including indexes;

Table altered.

Best regards

Maxim

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 07 2007 - 12:58:35 CDT