Purpose
The main goal of this document is to address errors and misunderstandings about the EXCHANGE (SUB)PARTITION command.Scope and Application
It may be helpful for all people, DBA's, Developers and End users who are concerned byusing EXCHANGE command for their future needs.
Exchange Partitions - Common Problems
Overview
The partitioning option was introduced with the 8.0 version of Oracle Enterprise Edition. It was mainly issued for the VLDB databases which contains very big tables and indexes. Before this kernel option was offered, it was difficult to maintain those objects as their sizes increased. Further, the performance for requests against those objects were not easy to optimize as their volume of data was very large. From Oracle8, parttioing allows us to break up tables and indexes into smaller components, called partitions which have their own storage attributes. This new possibility greatly improves the manageability and performance of VLDB databases.Oracle 8.0.X offers only one partitioning type:
- RANGE
Oracle 8.1.X offers two new choices:
- HASH
- COMPOSITE which is RANGE partitioned at a first level, and subpartitioned by HASH at a second level.
Oracle 9.0.1:
- LIST partitioning for which we must choose the values contained in each defined partition.
Oracle 9.0.2:
- COMPOSITE by RANGE at the first level, and by LIST at the second level.
With these different methods, Oracle brings many administrative commands like EXCHANGE which help to maintain this new Oracle Layer. The EXCHANGE (SUB)PARTITION command was introduced since the first 8.0.X version of Oracle Entreprise Edition.
Exchange Partition Principles
The EXCHANGE PARTITION command may convert one non partitioned table with:- a RANGE, HASH or LIST partition.
- a HASH or LIST subpartition.
The EXCHANGE PARTITION command may also convert, since the 8.1.6 version:
- one HASH partitioned table with any HASH subpartitions of a COMPOSITE RANGE-HASH partitioned table.
and since the 9.0.2 version:
- One LIST partitioned table with any LIST subpartitions of a COMPOSITE RANGE-LIST partitioned table.
This is a DDL order which modifies only some references in the Oracle Metadata. This operation can then be executed very quickly as no physical segments are moved.
In all cases, the structure of the table and the partition or subpartition being exchanged, including their partitioning keys, must be exactly the same. In the case of exchanging list partitions with RANGE-LIST subpartitions, the corresponding value list must also match.
If table contains LOB columns, then for each LOB column Oracle exchanges LOB data and LOB index partition or subpartition segments with corresponding LOB data and LOB index segments of table.
All of the segment attributes of the two objects (including tablespace and logging) are also exchanged. See <<Bug 1834530>> fixed in 8.1.7.4.
All statistics of the table and partition are exchanged, including table, column, index statistics, and histograms. See <<Note 101561.1>>.
Oracle invalidates any global indexes on the objects being exchanged by default. If you specify the UPDATE GLOBAL INDEXES clause, then Oracle will maintain the global indexes on the table whose partition is being exchanged. This new option was introduced with the 9.0.1 release. Global indexes on the non-partitioned table being exchanged remain invalid.
If you want the Local indexes set on(sub)partition tables be exchanged with the corresponding local or regular indexes, you must use the INCLUDING INDEXES clause. By default, the EXCLUDING INDEXES clause will be set. This clause is usable since the 8.1.5 version.
If you want Oracle not to check that each new row maps correctly into the associated (sub)partition , you must use the WITHOUT VALIDATION clause. By default, the WITH VALIDATION clause is set. Linked with that option, the EXCEPTION INTO clause may be added which will record the rows that don't map correctly.
Common Problems
A. ORA-1502: index '%s.%s' or partition of such index is in unusable state.When you exchange (sub)partitions, any Global Indexes become unusable. Then, when you execute
a request against this table which tries to use this index, you will get this error. You must rebuild the Global
Index if you want to use it again. To avoid this, you should use the UPDATE GLOBAL INDEXES keyword, then all the Global Indexes will stay usable on the partitioned table.
If you have defined a local index on the partition table, the (sub)partition index will be change to UNUSABLE status. You must then rebuild the (sub)partition index.
See <<Note 109700.1>> and <<Note 165917.1>>.
B. ORA-14099: all rows in table do not qualify for specified partition.
You try to exchange a non-partitioned, HASH or LIST partitioned table with a RANGE or LIST partition. Some rows aren't
mapped correctly with this RANGE or LIST partitioning and the WITH VALIDATION clause is used.
In this case, Oracle checks the partitioning key of each row exchanged and return this error if it's incorrect.
You can invalidate this checking by using the WITHOUT VALIDATION clause.
The example below will illustrate that:
SQL > CREATE TABLE SALES_BY_REGION (id INTEGER, qty INTEGER,
store_name VARCHAR(30), state_code VARCHAR(2),
sale_date DATE)
PARTITION BY LIST (state_code)
(
PARTITION region_east
VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')
tablespace TBS1,
PARTITION region_west
VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO')
tablespace TBS2,
PARTITION region_south
VALUES ('TX','KY','TN','LA','MS','AR','AL','GA')
tablespace TBS3,
PARTITION region_central
VALUES ('OH','ND','SD','MO','IL','MI','IA')
tablespace TBS4
);
Table created
SQL> CREATE TABLE SALES (id INTEGER, qty integer,
store_name VARCHAR(30), state_code VARCHAR(2),
sale_date DATE);
Table created
SQL> insert into SALES (state_code) values ('CA');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table SALES_BY_REGION exchange partition REGION_EAST with table SALES;
alter table sales_by_region exchange partition region_east with table sales_bis
*
ERROR at line 1:
ORA-14099: all rows in table do not qualify for specified partition
Now if you choose REGION_WEST which is the correct target on
SALES_BY_REGION, it will succeed.
SQL> alter table SALES_BY_REGION exchange partition REGION_WEST with table SALES;
Table altered.
See <<Note 135762.1>>
C. ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
ORA-14096: tables in ALTER TABLE EXCHANGE PARTITION must have the same number of columns.
ORA-14631: the partition bounds do not match the subpartition bounds of the partition.
ORA-14294: Number of partitions does not match number of subpartitions.
When you use the EXCHANGE PARTITION command, you must verify the following rules are observed:
- The column types and sizes must be the same between each segment exchanged --> ORA-14097. See <<Note 72332.1>>.
- The column number must be the same --> ORA-14096
- When you exchange LIST with RANGE-LIST segments, the definition of list subpartitions and partitions must be the same --> ORA-14631
- When you exchange HASH with RANGE-HASH segments, the number of hash subpartitions and partitions must be equal --> ORA-14294
See Bug 570525 for table created with CTAS command in version lower than 8.0.5.
When you want exchange a RANGE-HASH with HASH table, you must be sure that the hask key range is in the same order as the column definition, otherwise you will get the ORA-600[atbace0].
For the next issues, we create now the following RANGE_HASH table:
SQL> create table EMP_RANGE_HASH(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
SAL NUMBER(7,2)
)
partition by range (empno)
subpartition by hash (sal)
subpartitions 4
(
partition P1 values less than (10),
partition P2 values less than (20),
partition P3 values less than (30),
partition P4 values less than (MAXVALUE))
/
Table created
D. ORA-14291: cannot EXCHANGE a composite partition with a non-partitioned table.
If you try to exchange a non-partitioned table with a RANGE-HASH or
RANGE-LIST table, you will get this error.
SQL> create table EMP (
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
SAL NUMBER(7,2)
)
/
Table created
SQL> alter table emp_range_hash exchange partition P1 with table emp;
alter table EMP_RANGE_HASH exchange partition P1 with table EMP
*
ERROR at line 1:
ORA-14291: cannot EXCHANGE a composite partition with a non-partitioned table
E. ORA-14292: Partitioning type of table must match subpartitioning type of composite partition.
There must be a mapping between partitions and subpartitions when you exchange it.
The valid combinations are:
- HASH partitions with subpartitions of RANGE-HASH table.
- LIST partitions with subpartitions of RANGE-LIST table.
All the other combinations are not supported.
The example below will present a combination that is not supported.
SQL> create table EMP_RANGE(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 SAL NUMBER(7,2)
5 )
6 partition by range (empno)
7 (
8 partition P1 values less than (10),
9 partition P2 values less than (20),
10 partition P3 values less than (30),
11 partition P4 values less than (MAXVALUE));
Table created.
SQL> alter table EMP_RANGE_HASH exchange partition P2 with table EMP_RANGE;
alter table EMP_RANGE_HASH exchange partition P2 with table EMP_RANGE
*
ERROR at line 1:
ORA-14292: Partitioning type of table must match subpartitioning type of
composite partition
F. ORA-14276: EXCHANGE SUBPARTITION requires a non-partitioned, non-clustered table.
ORA-14095: ALTER TABLE EXCHANGE requires a non-partitioned, non-clustered table.
You cannot exchange a RANGE-HASH or RANGE-LIST subpartition with a partitioned or clustered table.
SQL> create table EMP_HASH(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 SAL NUMBER(7,2)
)
5 partition by hash (sal) partitions 4
/
Table created
SQL> alter table EMP_RANGE_HASH exchange subpartition SYS_SUBP41 with table EMP_HASH;
alter table EMP_RANGE_HASH exchange subpartition SYS_SUBP41 with table EMP_HASH
*
ERROR at line 1:
ORA-14276: EXCHANGE SUBPARTITION requires a non-partitioned, non-clustered
table
The EXCHANGE PARTITION will return an ORA-14095 if you try to use a partitioned
or clustered table.
G. ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
When you use the INCLUDING INDEXES clause, you are asking oracle to exchange all the local indexes defined with a target index, then for each local index
defined, there must be a local or non-partitioned index equivalent. If its not the case, you get the ORA-14098 code. This constraint doesn't affect the Global Indexes which becomes UNUSABLE except if the UPDATE GLOBAL INDEXES clause is used.
For example, if we create a Local Index on the EMP_HASH table shown above:
create index IDX_EMP_EMPNO on EMP_HASH(empno)
local;
The partition status is now displayed:
SQL> select index_name, partition_name, partition_position, status from user_ind_partitions
2 where index_name = 'IDX_EMP_EMPNO'
3 order by partition_position;
INDEX_NAME PARTITION_NAME PARTITION_POSITION STATUS
-------------------- ------------------------------ ------------------ --------
IDX_EMP_EMPNO SYS_P10393 1 USABLE
IDX_EMP_EMPNO SYS_P10394 2 USABLE
IDX_EMP_EMPNO SYS_P10395 3 USABLE
IDX_EMP_EMPNO SYS_P10396 4 USABLE
SQL> select table_name, partition_name, partition_position from user_tab_partitions
2 where table_name = 'EMP_HASH';
TABLE_NAME PARTITION_NAME PARTITION_POSITION
-------------------- ------------------------------ ------------------
EMP_HASH SYS_P10393 1
EMP_HASH SYS_P10394 2
EMP_HASH SYS_P10395 3
EMP_HASH SYS_P10396 4
You try now to exchange one partition of EMP_HASH with the non-partitioned EMP table defined above:
SQL> alter table EMP_HASH exchange partition SYS_P10393 with table EMP including indexes;
alter table EMP_HASH exchange partition SYS_P10393 with table EMP including indexes
*
ERROR at line 1:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
If you don't use the INCLUDING INDEXES keywords, this error message isn't
returned as Oracle doesn't try to exchange Local partition indexes.
It changes only the Local partition status index to UNUSABLE.
SQL> alter table EMP_HASH exchange partition SYS_P10393 with table EMP;
Table altered.
SQL> select index_name, partition_name, partition_position, status from user_ind_partitions
2 where index_name = 'IDX_EMP_EMPNO'
3 order by partition_position;
INDEX_NAME PARTITION_NAME PARTITION_POSITION STATUS
-------------------- ------------------------------ ------------------ --------
IDX_EMP_EMPNO SYS_P10393 1 UNUSABLE
IDX_EMP_EMPNO SYS_P10394 2 USABLE
IDX_EMP_EMPNO SYS_P10395 3 USABLE
IDX_EMP_EMPNO SYS_P10396 4 USABLE
If you have defined some functional indexes on the tables, their definitions aren't checked by Oracle, which may lead to corruption.
See <<Bug 1653112>> fixed with 8.1.7.4 patchset.
H. ORA-14128: FOREIGN KEY constraint mismatch in ALTER TABLE EXCHANGE PARTITION.
ORA-2266: unique/primary keys in table referenced by enabled foreign keys.
You cannot use the EXCHANGE (SUB)PARTITION command when a FOREIGN KEY or PRIMARY constraints are enabled on one of the exchanged tables, unless the reference table is empty.
On one table EMP_OTHER witch is identical to EMP, we define a PRIMARY KEY constraint
SQL> alter table EMP_OTHER
2 add constraint PRIM_EMP_OTHER PRIMARY KEY (empno);
Table altered.
The EMP table will reference it.
SQL> alter table EMP
2 add constraint FOREIGN_EMP foreign key(empno) references EMP_OTHER (empno);
Table altered.
We can verify now that these two constraints are enabled.
SQL> select table_name, constraint_name, constraint_type, status, deferred from user_constraints
2 where TABLE_NAME like 'EMP%';
TABLE_NAME CONSTRAINT_NAME C STATUS DEFERRED
-------------------- ------------------------------ - -------- ---------
EMP FOREIGN_EMP R ENABLED IMMEDIATE
EMP_OTHER PRIM_EMP_OTHER P ENABLED IMMEDIATE
2 rows selected.
Some records have been inserted in the two linked tables which follow the referential integrity constraint.
If you try now to exchange EMP and EMP_HASH:
SQL> alter table EMP_HASH exchange partition SYS_P10393 with table EMP including indexes;
alter table EMP_HASH exchange partition SYS_P10393 with table EMP including indexes
*
ERROR at line 1:
ORA-14128: FOREIGN KEY constraint mismatch in ALTER TABLE EXCHANGE PARTITION
On the REFERENCE table, you will get:
SQL> alter table EMP_HASH exchange partition SYS_P10393 with table EMP_OTHER;
alter table EMP_HASH exchange partition SYS_P10393 with table EMP_OTHER
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
You can disable the FOREIGN KEY constraint:
SQL> alter table EMP
2 disable constraints FOREIGN_EMP;
Table altered.
SQL> alter table EMP_HASH exchange partition SYS_P10393 with table EMP including indexes;
Table altered.
The EXCHANGE will succeed now.
I. ORA-14132: table cannot be used in EXCHANGE.
You can use all types of non-partitioned tables. The following column types aren't supported yet:
typed table
contains ADT columns
contains nested-table columns
contains REF columns
contains array columns
it is a nested table
Since the 8.1.X version, you can exchange IOT tables but some restrictions must be applied on it. See <<Note 198664.1>>.
J. ORA-14296: Table block size mismatch in ALTER TABLE EXCHANGE [SUB]PARTITION.
ORA-14297: Index block size mismatch in ALTER TABLE EXCHANGE [SUB]PARTITION.
ORA-14298: LOB column block size mismatch in ALTER TABLE EXCHANGE [SUB]PARTITION.
The new 9.0.X version offers the means to define different block size on different tablespaces. These block sizes rely on the DB_nK_CACHE_SIZE 9i parameter. By using it, you can stored the segments like TABLE, INDEX, OVERFLOW, LOB in different blocksize tablespaces.
The rule which must be respected is that when Oracle exchanges one segment with another, they must be contained in tablespaces with equal blocksize.
K. ORA-14130: Unique constraints mismatch in ALTER TABLE EXCHANGE PARTITION.
One of the tables named in the exchange partition command has a unique constraint In which there is no matching constraint defined on the other table. This can also happen if there is a constraint defined on the other table, but it does not match the constraint on the first table. For example, being enabled, validated or columns not in the same order.
In order to correct this problem, you must ensure that the unique constraints on both table match.
Niciun comentariu:
Trimiteți un comentariu