/* 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 (See ORA-14126.ora-code.com): 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?
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 (See ORA-14098.ora-code.com): 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);
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);
