ORA-14074 when adding partition at the beginning of a table

One of maintenance tasks for DBA's in our company is archiving old data. To ease maintenance for us we've created few big interval-date range partitioned tables to be able perform partition based operations on that tables.

We archive old partitions using exchange partition + transportable tablespace feature.

This time I won't concentrate on archiving partitions but adding old partitions back to table.


So how to add "old" partition at the beginning or in the middle of a table?

My first try was using 'alter table add partition' command but then I received error:
ORA-14074: partition bound must collate higher than that of the last partition



Small demo case with workaround...

First to create partitioned table with global and local indexes:

create table testtab (id number,
                      code number,
                      datum date)
partition by range (datum) (
    partition part_2009_10 values less than (to_date('01.11.2009','dd.mm.yyyy')),
    partition part_2009_11 values less than (to_date('01.12.2009','dd.mm.yyyy')),
    partition part_2009_12 values less than (to_date('01.01.2010','dd.mm.yyyy')),
    partition part_2010_01 values less than (to_date('01.02.2010','dd.mm.yyyy'))
    );

 
-- global index    
create unique index id_idx on testtab(id);


-- local indexes
create index code_idx on testtab(code)
local ( partition part_2009_10,
        partition part_2009_11,
        partition part_2009_12,
        partition part_2010_01);
        
        
-- create primary key
alter table testtab add constraint pk_id
    primary key (id) using index;

Populate table:

insert into testtab
select level,abs(dbms_random.random), trunc(sysdate)-level
from dual
connect by level<101;

commit;

Small check:

SQL> begin
  2  dbms_stats.gather_table_stats(ownname=>user,tabname=>'TESTTAB');
  3  end;
  4  /

PL/SQL procedure successfully completed.

select partition_name,high_value,num_rows as "NR", blocks
from user_tab_partitions
where table_name='TESTTAB';

PARTITION_NAME  HIGH_VALUE                                                  NR    BLOCKS
--------------- ---------------------------------------------------------- ----- -------
PART_2009_11    TO_DATE(' 2009-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')     30       5
PART_2009_12    TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')     31       5
PART_2010_01    TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')     17       5
PART_2009_10    TO_DATE(' 2009-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')     22       5

4 rows selected.

Now to "archive old partition":

-- create new table based on testtab for "archive" data
create table tab_2009_10
as select * from testtab where 1=2;

alter table tab_2009_10 modify (id not null);

create index code_2009_10_idx on tab_2009_10(code);


-- exchange "old" partition
alter table testtab exchange partition part_2009_10
with table tab_2009_10 including indexes update global indexes;


-- drop "old" partition prom partitioned table
alter table testtab drop partition part_2009_10 update global indexes;


-- select
col partition_name for a15
col high_value for a90

select partition_name,high_value
from user_tab_partitions
where table_name='TESTTAB'
order by 1;

PARTITION_NAME  HIGH_VALUE
--------------- -------------------------------------------------------------
PART_2009_11    TO_DATE(' 2009-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
PART_2009_12    TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
PART_2010_01    TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')

3 rows selected.



-- select "archived" data from newly created table
select min(datum),max(datum),count(*)
from tab_2009_10;

MIN(DATUM)          MAX(DATUM)            COUNT(*)
------------------- ------------------- ----------
10.10.2009 00:00:00 31.10.2009 00:00:00         22

1 row selected.

It is now safe to export tab_2009_10 table and archive it somewhere else. But what if I need this archived data to be a part of partitioned table again... First I will try to add partition using "add partition" command:

-- add partition test
SQL> alter table testtab add partition part_2009_10
  2      values less than (to_date('01.11.2009','dd.mm.yyyy'));
alter table testtab add partition part_2009_10
                                  *
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition

So little workaround is needed... We could use "split partition" command to add partition at the beginning of the table:

alter table testtab split partition part_2009_11
at (to_date('01.11.2009','dd.mm.yyyy'))
into (partition part_2009_10,partition part_2009_11);

Little check:

col partition_name for a15
col high_value for a90

select partition_name,high_value
from user_tab_partitions
where table_name='TESTTAB'
order by 1;

PARTITION_NAME  HIGH_VALUE
--------------- --------------------------------------------------------------
PART_2009_10    TO_DATE(' 2009-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
PART_2009_11    TO_DATE(' 2009-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
PART_2009_12    TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
PART_2010_01    TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')

So PART_2009_10 is again part of partitioned table. Now it is possible to insert archived rows in partitioned table using "insert ... select" command:

insert into testtab
    select * from tab_2009_10;

This is little inconvenient solution when we have to insert many rows, so there is another solution using exchange partition:

alter table testtab exchange partition part_2009_10
  2  with table tab_2009_10 including indexes update global indexes;
alter table testtab exchange partition part_2009_10
*
ERROR at line 1:
ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION

To avoid this error I've disabled and re-enabled constraint of partitioned table. NOTE! I don't know if this is appropriate solution so I would be tankful if someone could suggest me better solution.

SQL> alter table testtab disable novalidate constraint PK_ID;

Table altered.

-- because of disable novalidate pk_id lost not null constraint
SQL> alter table tab_2009_10 modify (id null);

Table altered.

SQL> alter table testtab exchange partition part_2009_10
  2  with table tab_2009_10 including indexes update global indexes;

Table altered.

SQL> alter table testtab enable validate constraint PK_ID;

Table altered.

For final check I will query "old" data in partitioned table:

SQL> select min(datum),max(datum),count(*)
  2   from testtab partition (part_2009_10);

MIN(DATUM)          MAX(DATUM)            COUNT(*)
------------------- ------------------- ----------
10.10.2009 00:00:00 31.10.2009 00:00:00         22

1 row selected.

References: Metalink [ID 727306.1]