Add columns and column attributes

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

Syntax

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]... ) ]

column_definition:
column [ datatype [ COLLATE column_collation_name ] ]
  [ SORT ] [ VISIBLE | INVISIBLE ]
  [ DEFAULT [ ON NULL ] expr | identity_clause ]
  [ ENCRYPT encryption_spec ]
  [ { inline_constraint }...
  | inline_ref_constraint
  ]

virtual_column_definition:
column [ datatype [ COLLATE column_collation_name ] ]
  [ VISIBLE | INVISIBLE ]
  [ GENERATED ALWAYS ] AS (column_expression) [ VIRTUAL ]
  [ evaluation_edition_clause ] [ unusable_editions_clause ]
  [ inline_constraint [ inline_constraint ]... ]

column_properties:
{ object_type_col_properties
| nested_table_col_properties
| { varray_col_properties | LOB_storage_clause }
    [ (LOB_partition_storage [, LOB_partition_storage ]...) ]
| XMLType_column_properties
}...

out_of_line_part_storage:
PARTITION partition
  { nested_table_col_properties | LOB_storage_clause | varray_col_properties }
    [ nested_table_col_properties | LOB_storage_clause | varray_col_properties ]...
[ ( SUBPARTITION subpartition
   { nested_table_col_properties | LOB_storage_clause | varray_col_properties }
     [ nested_table_col_properties | LOB_storage_clause | varray_col_properties
     ]...
    [, SUBPARTITION subpartition
     { nested_table_col_properties | LOB_storage_clause | varray_col_properties }
       [ nested_table_col_properties | LOB_storage_clause | varray_col_properties
       ]...
    ]...
  )
]

Supported operations

• Adding a regular column is supported. For more information, see Regular columns. Example:

  ALTER TABLE T ADD C1 NUMBER;
  

• Adding a virtual column is supported. For more information, see Virtual columns. Example:

  ALTER TABLE T ADD C1 NUMBER GENERATED ALWAYS AS (C+1);
  

• Adding the visibility attribute to a column by using the VISIBLE | INVISIBLE option is supported. Example:

  ALTER TABLE T ADD C1 NUMBER INVISIBLE;
  

• Adding the NOT NULL and DEFAULT VALUE inline constraints is supported. Example:

  ALTER TABLE T ADD C1 NUMBER NOT NULL DEFAULT 1;
  

• Adding multiple columns at a time is supported. Example:

  ALTER TABLE T ADD (C1 NUMBER, C2 NUMBER, C3 NUMBER);
  ALTER TABLE T ADD (C1 NUMBER) ADD (C2 NUMBER) ADD (C3 NUMBER);
  

Unsupported operations

• Adding a virtual column without specifying the field type is not supported. An error will be returned when you perform this operation.

• Defining an auto-increment column by using the identity_clause clause is not supported. An error will be returned when you perform this operation.

Ignored clauses and options

• The out_of_line_part_storage option for specifying the storage characteristics of a new column in the partitioned table.

• The options for adding the UNIQUE, PRIMARY KEY, and CHECK inline constraints.

• The constraint_state option for specifying the constraint status.

• The constraint name option for specifying a constraint name.

• The column_properties option for modifying the object type, nested table, and storage characteristics of a VARRAY or LOB column.