Home » Other » General » Help verifying a fix on 11.2 (11.2)
Help verifying a fix on 11.2 [message #442664] Tue, 09 February 2010 16:19 Go to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I've raised an SR with Oracle against v10.2.0.4. They have indicated that it is fixed in 11.2 and asked me to justify a backport to 10.2.0.4. In truth, it is not important to me because we have a workaround, but I need more convincing that the bug is fixed.

Can someone run my test script on 11.2 to check?

The bug is fairly self-explanatory if you look at the test script. In short, Oracle throws a spurious Unique Constraint violation during a Partition Exchange caused by NULLs. They pointed me to metalink 6955210, which is related to a similar problem but using the PARALLEL option (parallel rebuild of indexes). I'm not convinced they are the same.

I would greatly appreciate it if anyone could post the results of this script run on 11.2

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;


Ross Leishman
Re: Help verifying a fix on 11.2 [message #442796 is a reply to message #442664] Wed, 10 February 2010 11:38 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
doesn't look fixed to me!
  • Attachment: pex_bug.lst
    (Size: 4.53KB, Downloaded 1941 times)
Re: Help verifying a fix on 11.2 [message #442817 is a reply to message #442796] Wed, 10 February 2010 16:07 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Legend! Thanks John.
Previous Topic: ORA-604 signalled during: ALTER TABLESPACE
Next Topic: connection problem with database
Goto Forum:
  


Current Time: Thu Mar 28 12:17:43 CDT 2024