修改、删除和新增分区 - 云数据库 OceanBase

本文为您介绍迁移 Oracle 数据库的数据至 OceanBase 数据库 Oracle 租户时，ALTER TABLE DDL 修改、删除和新增分区的支持转换范围。

总览

alter_table_partitioning:
{ modify_table_default_attrs
| alter_automatic_partitioning
| alter_interval_partitioning
| set_subpartition_template
| modify_table_partition
| modify_table_subpartition
| move_table_partition
| move_table_subpartition
| add_external_partition_attrs
| add_table_partition
| coalesce_table_partition
| drop_external_partition_attrs
| drop_table_partition
| drop_table_subpartition
| rename_partition_subpart
| truncate_partition_subpart
| split_table_partition
| split_table_subpartition
| merge_table_partitions
| merge_table_subpartitions
| exchange_partition_subpart
}

modify_table_default_attrs:
MODIFY DEFAULT ATTRIBUTES
   [ FOR partition_extended_name ]
   [ DEFAULT DIRECTORY directory ]
   [ deferred_segment_creation ]
   [ read_only_clause ]
   [ indexing_clause ]
   [ segment_attributes_clause ]
   [ table_compression ]
   [ inmemory_clause ]
   [ PCTTHRESHOLD integer ]
   [ prefix_compression ]
   [ alter_overflow_clause ]
   [ { LOB (LOB_item) | VARRAY varray } (LOB_parameters) ]...

alter_automatic_partitioning:
{ SET PARTITIONING { AUTOMATIC | MANUAL }
| SET STORE IN ( tablespace [, tablespace ]... )
}

alter_interval_partitioning:
{ SET INTERVAL ( [expr] )
| SET STORE IN ( tablespace [, tablespace]... )
}

set_subpartition_template:
SET SUBPARTITION TEMPLATE
   { ( range_subpartition_desc [, range_subpartition_desc]... )
   | ( list_subpartition_desc [, list_subpartition_desc]... )
   | ( individual_hash_subparts [, individual_hash_subparts]... )
   | ()
   | hash_subpartition_quantity
   }
modify_table_partition:
{ modify_range_partition
| modify_hash_partition
| modify_list_partition
}

modify_table_subpartition:
MODIFY subpartition_extended_name
{ allocate_extent_clause
| deallocate_unused_cluse
| shrink_clause
| { { LOB LOB_item | VARRAY varray } (modify_LOB_parameters) }...
| [ REBUILD ] UNUSABLE LOCAL INDEXES
| { ADD | DROP } VALUES ( list_values )
| read_only_clause
| indexing_clause
}

move_table_partition:
MOVE partition_extended_name
   [ MAPPING TABLE ]
   [ table_partition_description ]
   [ filter_condition]
   [ update_index_clauses ]
   [ parallel_clause ]
   [ allow_disallow_clustering ]
   [ ONLINE ]

move_table_subpartition:
MOVE subpartition_extended_name [ indexing_clause ]
     [ partitioning_storage_clause ]
     [ update_index_clauses ]
     [ filter_condition ]
     [ parallel_clause ]
     [ allow_disallow_clustering ]
     [ ONLINE ]

add_external_partition_attrs:
ADD EXTERNAL PARTITION ATTRIBUTES external_table_clause 
 [ REJECT LIMIT ]pre

add_table_partition:
ADD {
PARTITION [ partition ] add_range_partition_clause
  [, PARTITION [ partition ] add_range_partition_clause ]...
| PARTITION [ partition ] add_list_partition_clause
  [, PARTITION [ partition ] add_list_partition_clause ]...
| PARTITION [ partition ] add_system_partition_clause
  [, PARTITION [ partition ] add_system_partition_clause ]...
  [ BEFORE { partition_name | partition_number } ]
| PARTITION [ partition ] add_hash_partition_clause
} [ dependent_tables_clause ]

coalesce_table_partition:
COALESCE PARTITION
  [ update_index_clauses ]
  [ parallel_clause ]
  [ allow_disallow_clustering ]

drop_external_partition_attrs:
DROP EXTERNAL PARTITION ATTRIBUTES

drop_table_partition:
DROP partition_extended_names
  [ update_index_clauses [ parallel_clause ] ]

drop_table_subpartition:
DROP subpartition_extended_names
  [ update_index_clauses [ parallel_clause ] ]

rename_partition_subpart:
RENAME { partition_extended_name
       | subpartition_extended_name
       } TO new_name

truncate_partition_subpart:
TRUNCATE { partition_extended_names | subpartition_extended_names }
   [ { DROP [ ALL ] | REUSE } STORAGE ]
   [ update_index_clauses [ parallel_clause ] ] [ CASCADE ]

split_table_partition:
SPLIT partition_extended_name
  { AT (literal [, literal]... )
    [ INTO ( range_partition_desc, range_partition_desc ) ]
  | VALUES ( list_values )
    [ INTO ( list_partition_desc, list_partition_desc ) ]
  | INTO ( { range_partition_desc [, range_partition_desc ]...
           | list_partition_desc [, list_partition_desc ]... }
         , partition_spec )
  } [ split_nested_table_part ]
    [ filter_condition ]
    [ dependent_tables_clause ]
    [ update_index_clauses ]
    [ parallel_clause ]
    [ allow_disallow_clustering ]
    [ ONLINE ]

split_table_subpartition:
SPLIT subpartition_extended_name
  { AT ( literal [, literal]... )
    [ INTO ( range_subpartition_desc, range_subpartition_desc ) ]
  | VALUES ( list_values )
    [ INTO ( list_subpartition_desc, list_subpartition_desc ) ]
  | INTO ( { range_subpartition_desc [, range_subpartition_desc ]...
           | list_subpartition_desc [, list_subpartition_desc ]... }
         , subpartition_spec )
  } [ filter_condition ]
    [ dependent_tables_clause ]
    [ update_index_clauses ]
    [ parallel_clause ]
    [ allow_disallow_clustering ]
    [ ONLINE ]

merge_table_partitions:
MERGE PARTITIONS partition_or_key_value
   { , partition_or_key_value [, partition_or_key_value ]...
   | TO partition_or_key_value }
   [ INTO partition_spec ]
   [ filter_condition ]
   [ dependent_tables_clause ]
   [ update_index_clauses ]
   [ parallel_clause ]
   [ allow_disallow_clustering ]

merge_table_subpartitions:
MERGE SUBPARTITIONS subpartition_or_key_value
   { , subpartition_or_key_value [, subpartition_or_key_value ]...
   | TO subpartition_or_key_value }
   [ INTO { range_subpartition_desc 
          | list_subpartition_desc
          }
   ]
   [ filter_condition ]
   [ dependent_tables_clause ]
   [ update_index_clauses ]
   [ parallel_clause ]
   [ allow_disallow_clustering ]

exchange_partition_subpart:
EXCHANGE { partition_extended_name
         | subpartition_extended_name
         }
   WITH TABLE [ schema. ] table
   [ { INCLUDING | EXCLUDING } INDEXES ]
   [ { WITH | WITHOUT } VALIDATION ]
   [ exceptions_clause ]
   [ update_index_clauses [ parallel_clause ] ]
   [ CASCADE ]

支持的范围

支持 drop_table_partition 子句删除分区，详情请参见删除分区。
支持 drop_table_subpartition 子句删除子分区，详情请参见删除子分区。
支持 add_table_partition 子句添加分区和子分区，详情请参见添加分区和子分区。
支持 modify_table_partition 子句修改分区，详情请参见修改分区。
支持 truncate_partition_subpart 子句删除分区数据，详情请参见删除分区数据。

不支持的范围

不支持 modify_table_subpartition 子句修改子分区，会报错。
不支持 modify_table_default_attrs 子句给表的属性指定新的默认值，会报错。
不支持 alter_automatic_partitioning 子句修改自动分区表，会忽略。
不支持 alter_interval_partitioning 子句修改 interval 分区表，会报错。
不支持 set_subpartition_template 子句为每个表分区创建或替换现有的默认 range、list 或 hash 子分区，会报错。
不支持 move_table_partition 子句将分区移动至另一个 segment，会报错。
不支持 move_table_subpartition 子句将子分区移动至另一个 segment，会报错。
不支持 add_external_partition_attrs 子句向分区表添加外部参数，会报错。
不支持 coalesce_table_partition 子句选择最后一个 hash 分区，将其内容分布至一个或多个剩余的由散列函数确定的分区中，然后删除最后一个分区，会报错。
不支持 drop_external_partition_attrs 子句在分区表中删除外部参数，会报错。
不支持 rename_partition_subpart 子句将表分区或子分区重命名为新名称，会报错。
不支持 split_table_partition 子句将一个分区拆分为多个具有不重叠值列表的新分区，会报错。
不支持 split_table_subpartition 子句将一个子分区拆分为多个具有不重叠值列表的新子分区，会报错。
不支持 merge_table_partitions 子句合并分区，会报错。
不支持 merge_table_subpartitions 子句合并子分区，会报错。
不支持 exchange_partition_subpart 子句来交换数据和索引 segment，会报错。