Drop partitions

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

Syntax

drop_table_partition:
DROP partition_extended_names
  [ update_index_clauses [ parallel_clause ] ]

partition_extended_names:
{ PARTITION | PARTITIONS }
partition | { FOR ( partition_key_value [, partition_key_value ]... ) }
  [, partition | { FOR ( partition_key_value [, partition_key_value ]... ) } ]...

update_index_clauses:
{ update_global_index_clause
| update_all_indexes_clause
}

update_global_index_clause:
{ UPDATE | INVALIDATE } GLOBAL INDEXES

update_all_indexes_clause:
UPDATE INDEXES
   [ ( index ( update_index_partition
             | update_index_subpartition
             )
        [, index ( update_index_partition
                 | update_index_subpartition
                 )
        ]...
      )
   ]

parallel_clause:
{ NOPARALLEL | PARALLEL [ integer ] }

Supported operations

• Dropping a partition with a specified partition name is supported. Example:

  ALTER TABLE T DROP PARTITION P0,P1
  ALTER TABLE T DROP PARTITIONS P0,P1
  

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

  ALTER TABLE T DROP PARTITION P0 UPDATE GLOBAL INDEXES;
  

Unsupported operations

• Dropping a partition with a specified partition key value is not supported. An error will be returned when you perform this operation. Example:

  ALTER TABLE T DROP PARTITION FOR(1);
  

• Invalidating global indexes by using the INVALIDATE GLOBAL INDEXES clause is not supported. This clause will be converted into the UPDATE GLOBAL INDEXES clause.

Ignored clauses and options

• The update_all_indexes_clause clause for updating indexes in a table.

• The parallel_clause clause for modifying the default query and DML parallelism for a table.