Partitioning problems - Oracle 8.1.7.4

From: <Tony.Adolph_at_o2.com>
Date: Fri, 4 Feb 2005 11:02:28 +0100
Message-ID: <OF96DE6B79.D8F0559D-ONC1256F9E.003697FD-C1256F9E.00370A0E@viaginterkom.de>

/*
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:

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

Received on Fri Feb 04 2005 - 05:06:28 CST