SQL> SQL> SELECT * from v$version ; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production SQL> SQL> SQL> /* SQL> CREATE 2 TEST TABLES: SQL> SQL> PEX_BUG: partitioned table SQL> PEX_BUG2: non partitioned table - same structure as PEX_BUG otherwise SQL> */ SQL> SQL> create table pex_bug (a number , b number) 2 partition by range (a) 3 (partition p1 values less than (10) 4 ,partition p2 values less than (maxvalue) 5 ); Table created. SQL> SQL> create table pex_bug2 (a number , b number); Table created. SQL> SQL> /* SQL> INSERT TEST DATA SQL> SQL> Two rows in each partition (one with NULL value for B), and two rows in the non-partitioned table SQL> */ SQL> SQL> insert into pex_bug (a,b) values (1, null); 1 row created. SQL> insert into pex_bug (a,b) values (2, 2); 1 row created. SQL> insert into pex_bug (a,b) values (100, null); 1 row created. SQL> insert into pex_bug (a,b) values (101, 101); 1 row created. SQL> insert into pex_bug2 (a,b) values (1, null); 1 row created. SQL> insert into pex_bug2 (a,b) values (2, 2); 1 row created. SQL> SQL> /* SQL> UNIQUE KEY CONSTRAINT SQL> SQL> Create a Unique constraint on the NULL column B. SQL> Disable the constraint on PEX_BUG2 so that we may to a Partition Exchange SQL> SQL> Note that even though there are 2 NULLs in the partitioned table, SQL> the unique constraint is not violated SQL> */ SQL> alter table pex_bug 2 add constraint pex_bug_uk unique (b); Table altered. SQL> SQL> alter table pex_bug2 2 add constraint pex_bug2_uk unique (b) 3 disable validate; Table altered. SQL> SQL> SQL> /* SQL> DISPLAY THE DATA before running the exchange. One row in each table SQL> */ SQL> select * from pex_bug partition (p1); A B ---------- ---------- 1 2 2 SQL> select * from pex_bug partition (p2); A B ---------- ---------- 100 101 101 SQL> select * from pex_bug2; A B ---------- ---------- 1 2 2 SQL> SQL> /* SQL> EXCHANGE P1 of the partitioned table with the non-partitioned table SQL> SQL> THIS WILL FAIL WITH A UNIQUE KEY VIOLATION!!!! SQL> SQL> But it should not fail because NULLs are not considered duplicates in a Unique constraint SQL> */ SQL> alter session set sql_trace = true; Session altered. SQL> SQL> alter table pex_bug 2 exchange partition p1 3 with table pex_bug2 4 including indexes 5 update global indexes; alter table pex_bug * ERROR at line 1: ORA-00001: unique constraint (SYS.PEX_BUG_UK) violated SQL> SQL> alter session set sql_trace = false; Session altered. SQL> SQL> /* SQL> Out of interest, let's drop the unique constraint but keep the Unique index SQL> */ SQL> alter table pex_bug 2 drop constraint pex_bug_uk 3 keep index; Table altered. SQL> SQL> alter table pex_bug2 2 drop constraint pex_bug2_uk 3 keep index; Table altered. SQL> SQL> /* SQL> Now try that exchange again SQL> */ SQL> alter table pex_bug 2 exchange partition p1 3 with table pex_bug2 4 including indexes 5 update global indexes; Table altered. SQL> SQL> /* SQL> IT WORKED! SQL> SQL> Now lets re-create the constraint SQL> */ SQL> alter table pex_bug 2 add constraint pex_bug_uk unique (b); Table altered. SQL> SQL> SQL> spool off