Partitioning problems - Oracle 8.1.7.4

/*
Hi Oracle partitioning gurus,
I am trying to setup a group of tables partitioned on a date column so 
that monthly
a new partition can be added and the oldest partition can be archived.

I have created 2 extra tables for each main table. One is a temporary 
table that is used
to swap partitions in an out of and a 3rd table, the archive.

I have tried to use local indexes where possible to simplify the exchange 
operation, ie.
reduce the work oracle has to do.  But I have had to create a global index 
for the PK index 
as the range column is not in the PK.  See below for example setup.

I have written a couple of SPs, one to add a new partition and the other 
to swap the oldest
partition from the main table to the archive via temp.

Problems:

1. When adding new partition:
------------------------------
I understood that to stop any global index going 'UNUSABLE' when adding a 
new partition
I must use something like:
        alter table TONYTAB
      split partition P04 at (to_date('200504','yyyymm'))
      into (partition P04, partition p05)
      UPDATE GLOBAL INDEXES;
But I get this error:
ORA-14126: only a <parallel clause> may follow description(s) of resulting 
partitions

When I drop the "UPDATE GLOBAL INDEXES" it seems to work without 
invalidating the indexes.

What is going wrong here?  Is it because I have global and local indexes 
on the same table?

2. Exchanging partitions:
-------------------------- 

To swap the oldest partition from my main table my SP tries to do this:
alter table TONYTAB
 exchange partition P01 with table TONYTAB_TEMP
 including indexes without validation;
alter table TONYTAB_ARC
 exchange partition P01 with table TONYTAB_TEMP
 including indexes without validation;
 
But I get this error at the first exchange:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

I tried to drop the temp table's indexes but get the same error.

I can see that the index I_TONYTAB_1 is local for the main table and is a 
normal index
for the temp table, but thats it.  I can't be local for the temp table is 
its un-partitioned.

I've read Lewis Chpts 12 & 13 a couple of times now but seem to be missing 
something 
somewhere.

Any help would be greatly appreciated.


Cheers
Tony
*/
create table TONYTAB
        (
          CUSTOMER_ID       NUMBER(19) not null,
          SERVICE_ID        NUMBER(19) not null,
          TRACKING_ID       NUMBER(19) not null,
          TARGET            VARCHAR2(200) not null,
          PASSWORD          VARCHAR2(200) not null,
          SUCCESS           VARCHAR2(3) not null,
          CREATED_DATE      DATE not null,
          CLIENT_IP_ADDRESS VARCHAR2(50)
        ) 
partition by range (CREATED_DATE) (
partition p01 values less than (to_date('200501','YYYYMM')),
partition p02 values less than (to_date('200502','YYYYMM')),
partition p03 values less than (to_date('200503','YYYYMM')),
partition p04 values less than (maxvalue))
tablespace PAGENT00 pctfree 10 pctused 40 initrans 1 maxtrans 255
  storage (initial 8k next 1k minextents 1 maxextents 121 pctincrease 0);

-- global index as CREATED_DATE is not part of the PK
create unique index PK_TONYTAB on 
TONYTAB(CUSTOMER_ID,SERVICE_ID,TRACKING_ID)
tablespace PAGENTX00 online pctfree 10 initrans 2 maxtrans 255
  storage (initial 1k next 1k minextents 1 maxextents 121 pctincrease 0);

alter table TONYTAB
add constraint PK_TONYTAB primary key 
(CUSTOMER_ID,SERVICE_ID,TRACKING_ID) using index;

-- local index
create index I_TONYTAB_1 on
TONYTAB (CUSTOMER_ID,SERVICE_ID)
local
  tablespace PAGENTX00 pctfree 10 initrans 2 maxtrans 255
  storage (initial 1k next 1k minextents 1 maxextents 121 pctincrease 0);

------------------- ARCHIVE TABLE -------------------

create table TONYTAB_ARC
        (
          CUSTOMER_ID       NUMBER(19) not null,
          SERVICE_ID        NUMBER(19) not null,
          TRACKING_ID       NUMBER(19) not null,
          TARGET            VARCHAR2(200) not null,
          PASSWORD          VARCHAR2(200) not null,
          SUCCESS           VARCHAR2(3) not null,
          CREATED_DATE      DATE not null,
          CLIENT_IP_ADDRESS VARCHAR2(50)
        ) 
partition by range (CREATED_DATE) 
        (partition p00 values less than (to_date('01011970','DDMMYYYY')))
tablespace PAGENT00 pctfree 10 pctused 40 initrans 1 maxtrans 255
  storage (initial 8k next 1k minextents 1 maxextents 121 pctincrease 0);

-- global index as CREATED_DATE is not part of the PK
create unique index PK_TONYTAB_ARC on 
TONYTAB_ARC(CUSTOMER_ID,SERVICE_ID,TRACKING_ID)
tablespace PAGENTX00 pctfree 10 initrans 2 maxtrans 255
storage (initial 1k next 1k minextents 1 maxextents 121 pctincrease 0);

alter table TONYTAB_ARC
add constraint PK_TONYTAB_ARC primary key 
(CUSTOMER_ID,SERVICE_ID,TRACKING_ID) using index;

-- local index
create index I_TONYTAB_ARC_1 on
TONYTAB_ARC (CUSTOMER_ID,SERVICE_ID)
local
tablespace PAGENTX00 pctfree 10 initrans 2 maxtrans 255
storage (initial 1k next 1k minextents 1 maxextents 121 pctincrease 0);

--- TEMP TABLE used for the exchange partition operation (non-partitioned) 
----------

create table TONYTAB_TEMP
        (
          CUSTOMER_ID       NUMBER(19) not null,
          SERVICE_ID        NUMBER(19) not null,
          TRACKING_ID       NUMBER(19) not null,
          TARGET            VARCHAR2(200) not null,
          PASSWORD          VARCHAR2(200) not null,
          SUCCESS           VARCHAR2(3) not null,
          CREATED_DATE      DATE not null,
          CLIENT_IP_ADDRESS VARCHAR2(50)
        ) 
        tablespace PAGENT00 pctfree 10 pctused 40 initrans 1 maxtrans 255
  storage (initial 8k next 1k minextents 1 maxextents 121 pctincrease 0);

create unique index PK_TONYTAB_TEMP on 
TONYTAB_TEMP(CUSTOMER_ID,SERVICE_ID,TRACKING_ID)
tablespace PAGENTX00 pctfree 10 initrans 2 maxtrans 255
storage (initial 1k next 1k minextents 1 maxextents 121 pctincrease 0);

alter table TONYTAB_TEMP
add constraint PK_TONYTAB_TEMP primary key 
(CUSTOMER_ID,SERVICE_ID,TRACKING_ID) using index;

create index I_TONYTAB_TEMP_1 on
TONYTAB_TEMP (CUSTOMER_ID,SERVICE_ID)
  tablespace PAGENTX00 pctfree 10 initrans 2 maxtrans 255
  storage (initial 1k next 1k minextents 1 maxextents 121 pctincrease 0);




 

--
http://www.freelists.org/webpage/oracle-l

Other related posts: