Skip to main content

Search

Welcome to Tibero GTS!

Resolving Error When Dropping/Adding a Partition with a Middle Value in Partition Table (TBR-7163)

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.

 

 

 

 

Related to