修改、刪除和新增分區 - ApsaraDB for 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，會報錯。