ORA-14098 - can anyone see what is wrong here

From: <genegurevich_at_discoverfinancial.com>
Date: Thu, 7 Jun 2007 11:53:40 -0500
Message-ID: <OF6F4BE06B.2712D4C2-ON862572F3.005C4C50-862572F3.005CCCE9@discoverfinancial.com>

Everybody:

I am getting this error and I can not see what is the issue. Normally I am able to find the discrepancy quickly, but I just can't see any difference here. I have opened an SR with Oracle and getting a whole lot of nothing from them So if someone manages to see any reason for thie ora-14098 please let me know. Oracle version is 10.2.0.3

SQL> drop TABLE DLY_CPB ;

Table dropped.

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 TABLESPACE IDS_D_01
  7 NOLOGGING
  8 parallel 4
  9 PARTITION BY RANGE (PROC_DT_VAL)
 10 SUBPARTITION BY HASH (ACCT_KEY)
 11 SUBPARTITIONS 16
 12 (
 13 PARTITION P200703
 14 VALUES LESS THAN (20070401)
 15 TABLESPACE IDS_D_01
 16 NOLOGGING,
 17 PARTITION P200704
 18 VALUES LESS THAN (20070501)
 19 TABLESPACE IDS_D_01
 20 NOLOGGING
 21 )
 22 ;

Table created.

SQL>
SQL> CREATE UNIQUE INDEX DLY_CPB_PK ON DLY_CPB(ACCT_KEY, PROC_DT_VAL)   2 TABLESPACE IDS_X_01
  3 NOLOGGING
  4 LOCAL
  5 parallel 4
  6 ;

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>
SQL>
SQL> Drop   TABLE XCHG_DLY_CPB ;

Table dropped.

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 TABLESPACE IDS_D_01
  7 NOLOGGING
  8 parallel 4
  9 PARTITION BY HASH (ACCT_KEY)
 10 PARTITIONS 16
 11 ;

Table created.

SQL>
SQL>
SQL> CREATE UNIQUE INDEX XCHG_DLY_CPB_PK ON XCHG_DLY_CPB(ACCT_KEY,

PROC_DT_VAL)
  2 TABLESPACE IDS_X_01
  3 NOLOGGING
  4 parallel 4
  5 ;

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>
SQL>
SQL>
SQL>
SQL>
SQL> alter table dly_cpb exchange partition p200704 with table xchg_dly_cpb

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

                                                          *

ERROR at line 1:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

thank you

Gene Gurevich

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

Received on Thu Jun 07 2007 - 11:53:40 CDT