Oracle Database Discussions

Gave up on posting this on metaLink - 5 attempts all disappeared into thin air.

OS is Linux kernal version 2.6.16.13-4-smp #1 SMP x86_64 GNU/Linux

Oracle version is :

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

I am trying to 11g range interval partitioning for use in a archiving strategy which will move all data from a few tables to a corresponding partitioned tables on a weekly basis.

I have set up a test case which demonstrates the problem I am having.

In my test case I table SRC_TAB and ARC_TAB created as follows:

create table src_tab
(
id number not null
)
/

create table arc_tab
(
id number not null
)
partition by range (id)
interval (1000)
(
partition p1 values less than (1)
)
/

I then populate SRC_TAB with 999 rows where the ID ranges from 1-999:

insert into src_tab
select rownum
from dual
connect by rownum < 1000;

At this stage then we have ID 1-999 in SRC_TAB, and in ARC_TAB we have the following:

Table Partition Rows HIGH_VALUE
---------------------------------------------
ARC_TAB P1 0 1

Then I perform the partition excahnge as follows:

LOCK TABLE arc_tab
PARTITION FOR (1000)
IN SHARE MODE;

alter table arc_tab exchange partition for (1000) with table src_tab;

This results in 0 rows in SRC_TAB and 999 rows in a new partition SYS_P65 in ARC_TAB:

Table Partition Rows HIGH_VALUE
---------------------------------------------
ARC_TAB P1 0 1
ARC_TAB SYS_P65 999 1001

So far, so good.

Next I add 1499 rows to SRC_TAB, with ID range 1002-2500.

I then attempt as econd exchange as follows:

insert into src_tab
select 1001+rownum
from dual
connect by rownum < 1500;

LOCK TABLE arc_tab
PARTITION FOR (2600)
IN SHARE MODE;

alter table arc_tab exchange partition for (2600) with table src_tab;

The alter table give the following error:

ERROR at line 1:
ORA-14099: all rows in table do not qualify for specified partition

If we look at the ARC_TAB, we can see that a third partition SYS_P66 has been created with a HIGH_VALUE of 3001:

Table Partition Rows HIGH_VALUE
---------------------------------------------
ARC_TAB P1 0 1
ARC_TAB SYS_P65 999 1001
ARC_TAB SYS_P66 0 3001

This suggests then that the cuurent contents of the table SRC_TAB (ID range 1002-2500) should easily fit into the new partition, so I am at a loss as to why I am getting this error.

Any ideas?