Hello Tom;
One word before my question. Your site is great and very helpful. I have looked up many things and learned a lot of new stuff about ORACLE. Thank you :)
In the above post my colleague was asking for a table exchanged that failed because of an ORA-14130. Now we have changed some code of the skript so that it can be run anywhere:
set echo on
drop table mpa_test ;
-- CREATE BASE HELPER TABLE
create table mpa_test (
ARTNR NUMBER(6) NOT NULL
,FIRMA CHAR(2) NOT NULL
,PERIODE CHAR(6) NOT NULL
,WAGR CHAR(5) NOT NULL
,RGM CHAR(4) NOT NULL
,WERBEKZ CHAR(1) NOT NULL
,LIEFNR NUMBER(6) NOT NULL
,ALIAS VARCHAR2(13) NOT NULL
,VK_MGE NUMBER(12,2) NOT NULL
,BRUTTO_VKW NUMBER(16,2) NOT NULL
,NETTO_VKW NUMBER(16,2) NOT NULL
,EKW NUMBER(16,2) NOT NULL
,WAEHRG VARCHAR2(3) NOT NULL
,AKTION_ID NUMBER NOT NULL
,AKTION_TYP VARCHAR2(2) NOT NULL
,RETOUR VARCHAR2(1) NOT NULL
,AKTION_MGE NUMBER
,MGE_GE NUMBER
,ANZ_BON NUMBER
,DEKW NUMBER
)
partition by range (periode)
(partition wo_200943 values less than ('200944'));
-- INSERT AT LEAST ONE ROW INTO BASE HELPER TABLE
insert into mpa_test select rownum, '00', '200943', '1111', 'qwer', 'J', 123123, 'qwertzqwertzq'
,1, 1, 1, 1, 'EUR', 1, 'QQ', 'J', 1, 1, 1, 1
from user_objects
where rownum < 2;
-- UNIQUE (PRIMARY KEY) INDEX ON BASE HELPER TABLE
create unique index mpa_test_pk on mpa_test
(ARTNR,FIRMA,PERIODE,WAGR,RGM,WERBEKZ,LIEFNR,ALIAS,AKTION_ID,RETOUR)
nologging local;
alter table mpa_test add constraint mpa_test_pk primary key
(ARTNR,FIRMA,PERIODE,WAGR,RGM,WERBEKZ,LIEFNR,ALIAS,AKTION_ID,RETOUR);
-- INDEX 1 ON BASE HELPER TABLE
create index mpa_test_i1 on mpa_test
(aktion_typ, firma)
nologging local;
-- FUNCTION BASED INDEX 1 ON BASE HELPER TABLE
create index mpa_test_f1 on mpa_test
( CASE WHEN AKTION_ID>0 THEN AKTION_ID END)
nologging local;
-- ADD INIT_PLUNR AND INIT_MENGE TO BASE HELPER TABLE
alter table mpa_test add init_plunr varchar2(30);
alter table mpa_test add init_menge number;
-- UPDATE NULL COLUMNS IN BASE HELPER TABLE
update mpa_test set init_plunr = '#' where init_plunr is null;
-- NEW UNIQUE (PRIMARY KEY) INDEX ON BASE HELPER TABLE
alter table mpa_test drop primary key drop index;
create unique index mpa_test_pk on mpa_test
(ARTNR,FIRMA,WAGR,RGM,WERBEKZ,LIEFNR,ALIAS,AKTION_ID,RETOUR,INIT_PLUNR,PERIODE)
nologging unusable compress 3 local;
alter index mpa_test_pk rebuild partition wo_200943;
alter table mpa_test add constraint mpa_test_pk primary key
(ARTNR,FIRMA,WAGR,RGM,WERBEKZ,LIEFNR,ALIAS,AKTION_ID,RETOUR,INIT_PLUNR,PERIODE);
drop table mpa_t ;
-- CREATE EXCHANGE HELPER TABLE
create table mpa_t as select * from mpa_test where 1 = 2;
-- UNIQUE (PRIMARY KEY) INDEX ON EXCHANGE HELPER TABLE
create unique index mpa_t_pk on mpa_t
(ARTNR,FIRMA,WAGR,RGM,WERBEKZ,LIEFNR,ALIAS,AKTION_ID,RETOUR,INIT_PLUNR,PERIODE)
nologging compress 3;
alter table mpa_t add constraint mpa_t_pk primary key
(ARTNR,FIRMA,WAGR,RGM,WERBEKZ,LIEFNR,ALIAS,AKTION_ID,RETOUR,INIT_PLUNR,PERIODE);
-- FUNCTION BASED INDEX 1 ON EXCHANGE HELPER TABLE
create index mpa_t_f1 on mpa_t
( CASE WHEN AKTION_ID>0 THEN AKTION_ID END)
nologging;
-- INDEX 1 ON EXCHANGE HELPER TABLE
create index mpa_t_i1 on mpa_t
(aktion_typ, firma)
nologging;
-- EXCHANGE BASE HELPER TABLE WITH EXCHANGE HELPER TABLE
alter table mpa_test exchange partition wo_200943 with table mpa_t including indexes;
select count(*) from mpa_test;
select count(*) from mpa_t;
When I run this code I'm getting the following:
alter table mpa_test exchange partition wo_200943 with table mpa_t including indexes
*
ERROR at line 1:
ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION
However the USER_IND_COLUMNS Views says, that the indexes are matching exactly.
When there are no data within the MPA_TEST table the exchange works, as well if we skip the function-based index (mpa_test_f1). The problem began with the addition of the INIT_PLUNR column.
We are using:
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Thanks in advance.
Regards,
Florian