Modify column attributes - ApsaraDB for OceanBase - Alibaba Cloud Documentation Center

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

Syntax

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
}

modify_col_properties:
column [ datatype ]
       [ COLLATE column_collation_name ]
       [ DEFAULT [ ON NULL ] expr | identity_clause | DROP IDENTITY ]
       [ { ENCRYPT encryption_spec } | DECRYPT ]
       [ inline_constraint ... ]
       [ LOB_storage_clause ]
       [ alter_XMLSchema_clause ]

modify_virtcol_properties:
column [ datatype ]
[ COLLATE collumn_collation_name ]
[ GENERATED ALWAYS ] AS (column_expression) [ VIRTUAL ]
evaluation_edition_clause [ unusable_editions_clause ]

modify_col_visibility:
column { VISIBLE | INVISIBLE }

modify_col_substitutable:
COLUMN column
[ NOT ] SUBSTITUTABLE AT ALL LEVELS
[ FORCE ]

Supported operations

Changing the column type is supported. For more information about the data type mappings, see Data type conversion. Sample code:
```
ALTER TABLE T MODIFY C1 CHAR;
```
Modifying the NOT NULL and DEFAULT VALUE constraints is supported. Sample code:
```
ALTER TABLE T MODIFY C1 NUMBER DEFAULT 1 NOT NULL;
```
Modifying multiple columns at a time is supported. Sample code:
```
ALTER TABLE T MODIFY (C1 CHAR,C2 CHAR);
```

Unsupported operations

Modifying unique and primary key constraints is not supported. An error will be returned when you perform these operations.
Modifying a virtual column is not supported. An error will be returned when you perform this operation.
Modifying the ENCRYPT\DECRYPT attribute of a column is not supported. An error will be returned when you perform this operation.
Modifying the visibility of a column by using the column { VISIBLE | INVISIBLE } option 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 options for modifying the CHECK and REFERENCES inline constraints.
The options for modifying the LOB storage characteristics.
The modify_col_substitutable option for setting or modifying the substitutability of a column object type.