Modify partitions

This topic describes the supported conversion scope of ALTER TABLE DDL operations for modifying partitions during data migration from an Oracle database to an Oracle tenant of OceanBase Database.

Syntax

modify_table_partition:
{ modify_range_partition
| modify_hash_partition
| modify_list_partition
}

modify_range_partition:
MODIFY partition_extended_name
   { partition_attributes
   | { add_range_subpartition
     | add_hash_subpartition
     | add_list_subpartition
     }
   | coalesce_table_subpartition
   | alter_mapping_table_clause
   | [ REBUILD ] UNUSABLE LOCAL INDEXES
   | read_only_clause
   | indexing_clause
   }

modify_hash_partition:
MODIFY partition_extended_name
  { partition_attributes
  | coalesce_table_subpartition
  | alter_mapping_table_clause
  | [ REBUILD ] UNUSABLE LOCAL INDEXES
  | read_only_clause
  | indexing_clause
  }

modify_list_partition:
MODIFY partition_extended_name
  { partition_attributes
  | { ADD | DROP } VALUES ( list_values )
  | { add_range_subpartition
    | add_list_subpartition
    | add_hash_subpartition
    }
  | coalesce_table_subpartition
  | [ REBUILD ] UNUSABLE LOCAL INDEXES
  | read_only_clause
  | indexing_clause
  }

partition_attributes:
[ { physical_attributes_clause
  | logging_clause
  | allocate_extent_clause
  | deallocate_unused_clause
  | shrink_clause
  }...
]
[ OVERFLOW
  { physical_attributes_clause
  | logging_clause
  | allocate_extent_clause
  | deallocate_unused_clause
  }...
]
[ table_compression ]
[ inmemory_clause ]
[ { { LOB LOB_item | VARRAY varray } (modify_LOB_parameters) }...]

coalesce_table_subpartition:
COALESCE SUBPARTITION subpartition
  [update_index_clauses]
  [parallel_clause]
  [allow_disallow_clustering]

read_only_clause:
{ READ ONLY } | { READ WRITE }

indexing_clause:
INDEXING { ON | OFF }

Supported operations

• Adding LIST, RANGE, and HASH subpartitions to a partition by specifying the partition name is supported. Example:

  ALTER TABLE T MODIFY PARTITION P0 ADD SUBPARTITION SP0;
  ALTER TABLE T MODIFY PARTITION P0 ADD SUBPARTITION VALUES(100);
  ALTER TABLE T MODIFY PARTITION P0 ADD SUBPARTITION VALUES LESS THAN(100);
  

• Updating indexes in a table by using the UPDATE GLOBAL INDEXES clause is supported.

Unsupported operations

• Adding LIST, RANGE, and HASH subpartitions to a partition by specifying the partition key value is not supported. An error will be returned when you perform these operations. Example:

  ALTER TABLE T MODIFY PARTITION FOR(1000) ADD SUBPARTITION SP1
  

• Adding values to and deleting values from LIST partition key values are not supported. An error will be returned when you perform these operations.

• Selecting the last HASH subpartition, distributing the data on it into one or more remaining subpartitions determined by the hash function, and then dropping the last subpartition by using the coalesce_table_partition clause is not supported. An error will be returned when you perform these operations.

Ignored clauses and options

• The read_only_clause clause for selecting the read/write mode.

• The indexing_clause clause for modifying the index attributes of a table partition.

• The partition_attributes clause for modifying partition parameters is not supported and will be ignored.