Add columns and column attributes

This topic describes the conversion scope of ALTER TABLE DDL operations for adding columns and column attributes during data migration from an Oracle database to the Oracle compatible mode 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 regular columns is supported. For more information, see Regular columns. Example:

  ALTER TABLE T ADD C1 NUMBER;
  

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

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

• Adding the VISIBLE | INVISIBLE attribute to columns is supported. Example:

  ALTER TABLE T ADD C1 NUMBER INVISIBLE;
  

• Adding the not null row constraint and default value attribute to columns 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 properties of the newly added columns in partitioned tables.

• The options for adding the unique, primary key, check, and foreign key constraints inline.

• The constraint_state option for specifying the constraint status.

• The constraint name option for specifying the constraint name.

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