Add columns and column attributes - ApsaraDB for OceanBase

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. Sample code:
```
ALTER TABLE T ADD C1 NUMBER;
```
Adding a virtual column is supported. For more information, see Virtual columns. Sample code:
```
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. Sample code:
```
ALTER TABLE T ADD C1 NUMBER INVISIBLE;
```
Adding the NOT NULL and DEFAULT VALUE inline constraints is supported. Sample code:
```
ALTER TABLE T ADD C1 NUMBER NOT NULL DEFAULT 1;
```

Adding multiple columns at a time is supported. Sample code:

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

Note

The following clauses and options will be ignored and will not be resolved or converted when they are specified in the synchronized DDL statements.

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.