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

  • From: Maxim Demenko <mdemenko@xxxxxxxxx>
  • To: genegurevich@xxxxxxxxxxxxxxxxxxxxx
  • Date: Thu, 07 Jun 2007 19:58:35 +0200

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
--
//www.freelists.org/webpage/oracle-l


Other related posts: