set echo on
spool pex_bug

SELECT * from v$version ;


/*
    CREATE 2 TEST TABLES:

    PEX_BUG:  partitioned table
    PEX_BUG2: non partitioned table - same structure as PEX_BUG otherwise
*/

create table pex_bug (a number , b number)
partition by range (a)
(partition p1 values less than (10)
,partition p2 values less than (maxvalue)
);

create table pex_bug2 (a number , b number);

/*
    INSERT TEST DATA

    Two rows in each partition (one with NULL value for B), and two rows in the non-partitioned table
*/

insert into pex_bug (a,b) values (1, null);
insert into pex_bug (a,b) values (2, 2);
insert into pex_bug (a,b) values (100, null);
insert into pex_bug (a,b) values (101, 101);
insert into pex_bug2 (a,b) values (1, null);
insert into pex_bug2 (a,b) values (2, 2);

/*
    UNIQUE KEY CONSTRAINT

    Create a Unique constraint on the NULL column B.
    Disable the constraint on PEX_BUG2 so that we may to a Partition Exchange

    Note that even though there are 2 NULLs in the partitioned table,
    the unique constraint is not violated
*/
alter table pex_bug
add constraint pex_bug_uk unique (b);

alter table pex_bug2
add constraint pex_bug2_uk unique (b)
disable validate;


/*
    DISPLAY THE DATA before running the exchange. One row in each table
*/
select * from pex_bug partition (p1);
select * from pex_bug partition (p2);
select * from pex_bug2;

/*
    EXCHANGE P1 of the partitioned table with the non-partitioned table

    THIS WILL FAIL WITH A UNIQUE KEY VIOLATION!!!!

    But it should not fail because NULLs are not considered duplicates in a Unique constraint
*/
alter session set sql_trace = true;

alter table pex_bug
exchange partition p1
with table pex_bug2
including indexes
update global indexes;

alter session set sql_trace = false;

/*
    Out of interest, let's drop the unique constraint but keep the Unique index
*/
alter table pex_bug
drop constraint pex_bug_uk
keep index;

alter table pex_bug2
drop constraint pex_bug2_uk
keep index;

/*
    Now try that exchange again
*/
alter table pex_bug
exchange partition p1
with table pex_bug2
including indexes
update global indexes;

/*
    IT WORKED!

    Now lets re-create the constraint
*/
alter table pex_bug
add constraint pex_bug_uk unique (b);


spool off

drop table pex_bug purge;
drop table pex_bug2 purge;