The partition exception for some large tables in the production library requires an online operation on the existing table to add the missing partition, because it is a production library, or a little cautious, and is available today, testing the impact of the Add&split partition on the global&local index, The test version is oracle11.2.0.4 and the process is as follows:
First, create the partition table:
CREATE TABLE TP1
(
C1 INT PRIMARY KEY,
C2 VARCHAR2 (10),
C3 CHAR (10)
)
Partition by range (C1)
(
Partition P1 values less than (6),
Partition P2 values less than (11),
Partition P3 values less than (16),
Partition MaxValue values less than (MaxValue));
However, when the add partition encountered a problem, the following error:
ORA-14074: The partition bounds must be adjusted above the last partition boundary
The root cause is the last MaxValue partition, and then create a partition table with no MaxValue:
CREATE TABLE TP2
(
C1 INT PRIMARY KEY,
C2 VARCHAR2 (10),
C3 CHAR (10)
)
Partition by range (C1)
(
Partition P1 values less than (6),
Partition P2 values less than (11),
Partition P3 values less than (16));
Then, create the local index:
Create INDEX IDX_TP2_C2 on TP2 (C2) local;
Because the primary key is a global index, you do not need to create another global indexes, we insert the data:
INSERT INTO TP2 vlaues (1, ' AAA ', ' AAA ');
INSERT INTO TP2 vlaues (2, ' AAA ', ' AAA ');
INSERT INTO TP2 vlaues (3, ' AAA ', ' AAA ');
INSERT INTO TP2 vlaues (4, ' AAA ', ' AAA ');
INSERT INTO TP2 vlaues (5, ' AAA ', ' AAA ');
INSERT INTO TP2 vlaues (6, ' AAA ', ' AAA ');
INSERT INTO TP2 vlaues (7, ' AAA ', ' AAA ');
INSERT INTO TP2 vlaues (8, ' AAA ', ' AAA ');
INSERT INTO TP2 vlaues (9, ' AAA ', ' AAA ');
INSERT INTO TP2 vlaues (' aaa ', ' AAA ');
Commit
We add partition:
ALTER TABLE TP2 Add PARTITION p10 values less than (51);
The add partition succeeds because there is no maxvalue partition, and the add partition has no effect on global and local indexes;
So, split partition:
ALTER TABLE TP2 SPLIT PARTITION p2 at (8) into (PARTITION P2, PARTITION P22);
If the split partition is found to invalidate the Golbal index, and the local index is normal, then the split partition has no effect on the global&local index after adding the Update indexes option for the split partition command:
ALTER TABLE TP2 SPLIT PARTITION p1 at (3) into (PARTITION P1, PARTITION p11) update indexes;
The above is the test results, recorded in this, so that other students and their own reference.
Effect of Add&split partition on global&local index in Oracle