Document Type | Troubleshooting
Category | Administration
Document Number | TADTS064
Issue
When dropping a partition with a middle value in the Partition Table and then adding a partition with the same value again, TBR-7163: Specified partition values are incorrect. error occurs.
Cause
In Tibero, when a partition in the middle range of the Partition Table is dropped and then attempted to be added again for that range only, an error occurs due to the constraint that the order of partition values between existing partitions must be maintained.
Solutions
1. Create Partition Table
drop table tibero.test;
create table tibero.test (logtime char(12))
partition by range (logtime)
(
partition p2017 values less than ('2017XXXXXXXX'),
partition p2018 values less than ('2018XXXXXXXX'),
partition p2019 values less than ('2019XXXXXXXX')
);
2. Drop Partition with Middle Value
alter table tibero.test drop partition p2018;
3. Error TBR-7163 Occurs When Adding Dropped Middle Value Partition Again
alter table tibero.test add partition p2018 values less than ('2018XXXXXXXX') ;โ TBR-7163: Specified partition values are incorrect. error occurs
3-1. Solution 1
Drop all partitions greater than the middle value partition, then add the dropped middle value partition again.
alter table tibero.test drop partition p2019;
alter table tibero.test add partition p2018 values less than ('2018XXXXXXXX') ;Test Result
SQL> drop table tibero.test;
TBR-7071: Schema object 'TIBERO.TEST' was not found or is invalid.
SQL> create table tibero.test (logtime char(12))
partition by range (logtime)
(
partition p2017 values less than ('2017XXXXXXXX'),
partition p2018 values less than ('2018XXXXXXXX'),
partition p2019 values less than ('2019XXXXXXXX')
); 2 3 4 5 6 7
Table 'TIBERO.TEST' created.
SQL> alter table tibero.test drop partition p2018;
Table 'TIBERO.TEST' altered.
SQL> alter table tibero.test add partition p2018 values less than ('2018XXXXXXXX') ; TBR-7163: Specified partition values are incorrect.
SQL> alter table tibero.test drop partition p2019;
Table 'TIBERO.TEST' altered.
SQL> alter table tibero.test add partition p2018 values less than ('2018XXXXXXXX') ;
Table 'TIBERO.TEST' altered.
3-2. Solution 2
Split the partition immediately above the middle value partition to replace the middle value partition.
alter table tibero.test split partition p2019 at ('2018XXXXXXXX')
into (partition p2018, partition p2019);Test Result
SQL> drop table tibero.test;
TBR-7071: Schema object 'TIBERO.TEST' was not found or is invalid.
SQL> create table tibero.test (logtime char(12))
partition by range (logtime)
(
partition p2017 values less than ('2017XXXXXXXX'),
partition p2018 values less than ('2018XXXXXXXX'),
partition p2019 values less than ('2019XXXXXXXX')
); 2 3 4 5 6 7
Table 'TIBERO.TEST' created.
SQL> alter table tibero.test drop partition p2018;
Table 'TIBERO.TEST' altered.
SQL> alter table tibero.test add partition p2018 values less than ('2018XXXXXXXX') ;
TBR-7163: Specified partition values are incorrect.
SQL> alter table tibero.test split partition p2019 at ('2018XXXXXXXX')
into (partition p2018, partition p2019);
Table 'TIBERO.TEST' altered.
4. Final Partition Information After Applying Solution
SELECT TABLE_NAME, PARTITION_NAME, BOUND, PARTITION_NO FROM DBA_TBL_PARTITIONS; TABLE_NAME PARTITION_NO PARTITION_NAME BOUND ----------------------- ---------------- ------------------- TEST 1 P2017 '2017XXXXXXXX' TEST 2 P2018 '2018XXXXXXXX' TEST 3 P2019 '2019XXXXXXXX' 3 rows selected.