Drop a partition

This topic describes the conversion scope of ALTER TABLE DDL operations for dropping a partition during data migration from an Oracle database to an Oracle compatible mode of OceanBase Database.

Overview

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 by specifying the partition name is supported. Example:

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

• Updating indexes in the 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 by specifying the partition key value is not supported. An error will be returned. Example:

  ALTER TABLE T DROP PARTITION FOR(1);
  

• The INVALIDATE GLOBAL INDEXES clause is not supported. It will be converted to the UPDATE GLOBAL INDEXES clause.

Ignored clauses and options

• The update_all_indexes_clause clause for updating indexes in the table.

• The parallel_clause clause for changing the default degree of parallelism for queries and DML operations in the table.