This article introduces you to the supported conversion scope of ALTER TABLE DDL regarding modifying, deleting, and adding column attributes when migrating data from an Oracle database to OceanBase Database's Oracle compatible mode.
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_clauseclause is supported. For more information, see Modify, drop, and add column attributes.Adding columns and constraint attributes by using the
add_column_clausesclause is supported. For more information, see Add columns and column attributes.Modifying column attributes by using the
modify_column_clausesclause is supported. For more information, see Modify column attributes.Dropping columns by using the
drop_column_clauseclause is supported. For more information, see Drop columns.
Unsupported operations
Modifying the result set retrieved from the database by using the
modify_collection_retrievalclause 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_clauseclause 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_propertiesclause is not supported. An error will be returned when you perform this operation.