All Products
Search
Document Center

ApsaraDB for OceanBase:Modify, drop, and add column attributes

Last Updated:Mar 17, 2023

This topic describes the supported conversion scope of ALTER TABLE DDL operations for modifying, dropping, and adding column attributes during data migration from an Oracle database to an Oracle tenant of OceanBase Database.

Syntax

column_clauses:
{ { add_column_clause
  | modify_column_clauses
  | drop_column_clause
  | add_period_clause
  | drop_period_clause
  }...
| rename_column_clause
| { modify_collection_retrieval }...
| { modify_LOB_storage_clause }...
| { alter_varray_col_properties }...
}

add_column_clause:
ADD
   ( {column_definition | virtual_column_definition
      [, column_definition | virtual_column_definition] ...
     } )
   [ column_properties ]
   [ ( out_of_line_part_storage [, out_of_line_part_storage]... ) ]

modify_column_clauses:
MODIFY
{ ( modify_col_properties | modify_virtcol_properties
    [, modify_col_properties | modify_virtcol_properties ]... )
| ( modify_col_visibility [, modify_col_visibility ]... )
| modify_col_substitutable
}

drop_column_clause:
{ SET UNUSED { COLUMN column
             | (column [, column ]...)
             }
  [ { CASCADE CONSTRAINTS | INVALIDATE }... ]
  [ ONLINE ]
| DROP { COLUMN column
       | (column [, column ]...)
       }
  [ { CASCADE CONSTRAINTS | INVALIDATE }... ]
  [ CHECKPOINT integer ]
| DROP { UNUSED COLUMNS
       | COLUMNS CONTINUE
       }
  [ CHECKPOINT integer ]
}

add_period_clause:
ADD ( period_definition )

drop_period_clause:
DROP ( PERIOD FOR valid_time_column )

rename_column_clause:
RENAME COLUMN old_name TO new_name

modify_collection_retrieval:
MODIFY NESTED TABLE collection_item
RETURN AS { LOCATOR | VALUE }

modify_LOB_storage_clause:
MODIFY LOB (LOB_item)
   (modify_LOB_parameters)

alter_varray_col_properties:
MODIFY VARRAY varray_item
   ( modify_LOB_parameters )

Supported operations

  • Renaming columns by using the rename_column_clause clause is supported. For more information, see Modify, drop, and add column attributes.

  • Adding columns and constraint attributes by using the add_column_clauses clause is supported. For more information, see Add columns and column attributes.

  • Modifying column attributes by using the modify_column_clauses clause is supported. For more information, see Modify column attributes.

  • Dropping columns by using the drop_column_clause clause is supported. For more information, see Drop columns.

Unsupported operations

  • Modifying the result set retrieved from the database by using the modify_collection_retrieval clause is not supported. An error will be returned when you perform this operation.

  • Modifying the physical attribute of the LOB_item by using the modify_LOB_storage_clause clause is not supported. An error will be returned when you perform this operation.

  • Modifying the LOB storage characteristics of the VARRAY type by using the alter_varray_col_properties clause is not supported. An error will be returned when you perform this operation.