Modify, drop, and add table attributes

This topic describes the supported conversion scope of ALTER TABLE DDL operations for modifying, dropping, and adding table attributes during data migration from an Oracle database to an Oracle-compatible tenant of OceanBase Database.

Syntax

alter_table_properties:
{ alter_table_properties_1 | { shrink_clause 
  | RENAME TO new_table_name
  | READ ONLY
  | READ WRITE 
  | REKEY encryption_spec 
  | DEFAULT COLLATION collation_name
  | [NO] ROW ARCHIVAL
  | ADD attribute_clustering_clause
  | MODIFY CLUSTERING [ clustering_when ] [ zonemap_clause ]
  | DROP CLUSTERING
  }
}

alter_table_properties_1:
{ { physical_attributes_clause
    | logging_clause
    | table_compression
    | inmemory_table_clause
    | ilm_clause
    | supplemental_table_logging
    | allocate_extent_clause
    | deallocate_unused_clause
    | { CACHE | NOCACHE }
    | RESULT_CACHE ( MODE {DEFAULT | FORCE} )
    | upgrade_table_clause
    | records_per_block_clause
    | parallel_clause
    | row_movement_clause
    | flashback_archive_clause
    }...
  } [ alter_iot_clauses ] [ alter_XMLSchema_clause ]

physical_attributes_clause:
[ { PCTFREE integer
  | PCTUSED integer
  | INITRANS integer
  | storage_clause
  }...
]

logging_clause:
{ LOGGING | NOLOGGING |  FILESYSTEM_LIKE_LOGGING }

table_compression:
COMPRESS
| ROW STORE COMPRESS [ BASIC | ADVANCED ]
| COLUMN STORE COMPRESS [  FOR { QUERY | ARCHIVE } [ LOW | HIGH ] ]
  [ [NO] ROW LEVEL LOCKING ]
| NOCOMPRESS

inmemory_table_clause:
[ { INMEMORY [ inmemory_attributes ] } | { NO INMEMORY } ]
[ inmemory_column_clause ]

ilm_clause:
ILM
{ ADD POLICY ilm_policy_clause
| { DELETE | ENABLE | DISABLE } POLICY ilm_policy_name
| DELETE_ALL | ENABLE_ALL | DISABLE_ALL
}

supplemental_table_logging:
{ ADD SUPPLEMENTAL LOG
  { supplemental_log_grp_clause | supplemental_id_key_clause }
    [, SUPPLEMENTAL LOG
       { supplemental_log_grp_clause | supplemental_id_key_clause }
    ]...
| DROP SUPPLEMENTAL LOG
  { supplemental_id_key_clause | GROUP log_group }
    [, SUPPLEMENTAL LOG
       { supplemental_id_key_clause | GROUP log_group }
    ]...
}

allocate_extent_clause:
ALLOCATE EXTENT
  [ ( { SIZE size_clause
      | DATAFILE 'filename'
      | INSTANCE integer
      } ...
    )
  ]

deallocate_unused_clause:
DEALLOCATE UNUSED [ KEEP size_clause ]

upgrade_table_clause:
UPGRADE [ [NOT ] INCLUDING DATA ]
   [ column_properties ]

records_per_block_clause:
{ MINIMIZE | NOMINIMIZE } RECORDS_PER_BLOCK

parallel_clause:
{ NOPARALLEL | PARALLEL [ integer ] }

row_movement_clause:
{ ENABLE | DISABLE } ROW MOVEMENT

flashback_archive_clause:
FLASHBACK ARCHIVE [flashback_archive] | NO FLASHBACK ARCHIVE

alter_iot_clauses:
{ index_org_table_clause
| alter_overflow_clause
| alter_mapping_table_clauses
| COALESCE
}

alter_XMLSchema_clause:
 { ALLOW ANYSCHEMA
  | ALLOW NONSCHEMA
  | DISALLOW NONSCHEMA
  }

Supported operations

Renaming a table by using the RENAME TO new_table_name clause is supported. Example:

ALTER TABLE SC.T RENAME TO T2;

Unsupported operations

• Adding the cluster attribute by using the ADD attribute_clustering_clause clause is not supported. An error will be returned when you perform this operation.

• Allowing or prohibiting the insertion of data on direct paths or data movement operations on tables by using the MODIFY CLUSTERING [ clustering_when ] [ zonemap_clause ] clause is not supported. An error will be returned when you perform this operation.

• Dropping the cluster attribute by using the DROP CLUSTERING clause is not supported. An error will be returned when you perform this operation.

• Modifying the physical attributes of a table by using the physical_attributes_clause clause is not supported. An error will be returned when you perform this operation.

• Modifying the logging attributes by using the logging_clause clause is not supported. An error will be returned when you perform this operation.

• Specifying whether to compress data segments in the database to reduce disk and memory usage by using the table_compression clause, which is supported only for heap tables. An error will be returned when you perform this operation.

• Enabling, disabling, or modifying the In-Memory Column Store table attribute by using the inmemory_table_clause clause is not supported. An error will be returned when you perform this operation.

• Adding, dropping, enabling, or disabling the automatic data optimization strategy for a table by using the ilm_clause clause is not supported. An error will be returned when you perform this operation.

• Adding or deleting redo log groups or one or more supplemental log columns in a redo log group by using the supplemental_table_logging clause is not supported. An error will be returned when you perform these operations.

• Explicitly allocating a new extent for tables, partitions, subpartitions, overflow data segments, LOB data segments, or LOB indexes by using the allocate_extent_clause clause is not supported. An error will be returned when you perform these operations.

• Explicitly releasing the unused space at the end of a table, partition, subpartition, overflow data segment, LOB data segment, or LOB index by using the deallocate_unused_clause clause is not supported. An error will be returned when you perform these operations.

• Specifying the caching mode for a table by using the { CACHE | NOCACHE } clause is not supported. An error will be returned when you perform this operation.

• Specifying the query result cache by using the RESULT_CACHE ( MODE {DEFAULT | FORCE} ) clause is not supported. An error will be returned when you perform this operation.

• Upgrading the metadata of the target table referenced by other tables by using the upgrade_table_clause clause is not supported. An error will be returned when you perform this operation.

• Limiting the number of records stored in a block by using the records_per_block_clause clause is not supported. An error will be returned when you perform this operation.

Ignored clauses and options

• The READ ONLY | READ WRITE clause for setting the read/write mode.

• The REKEY encryption_spec clause for generating new encryption keys or switching between algorithms.

• The DEFAULT COLLATION collation_name clause for changing the default collation of a table.

• The [NO] ROW ARCHIVAL option for enabling or disabling row archiving for a table.