Altering Table Definitions

Using ALTER TABLE syntax, you can respond to your evolving database schema requirements. The ability to change the definition of existing database objects facilitates ongoing maintenance. Furthermore, most of these options are both fast and efficient for large tables, because they consume fewer resources and less storage than having to stage data in a temporary table.

ALTER TABLE lets you perform the following table-level changes:

• Add and drop table columns.
• Rename a table.
• Add and drop constraints.
• Alter key constraint enforcement.
• Move a table to a new schema.
• Change a table owner.
• Change and reorganize table partitions.

ALTER TABLE has an ALTER COLUMN clause that lets you modify column definitions—for example, change their name or data type. For column-level changes, see Managing Table Columns.

Exclusive ALTER TABLE Clauses

The following ALTER TABLE clauses are exclusive: you cannot combine them with another ALTER TABLE clause:

• ADD COLUMN
• RENAME COLUMN
• SET SCHEMA
• PARTITION BY
• REORGANIZE
• REMOVE PARTITIONING
• RENAME [TO]
• OWNER TO

Using Consecutive ALTER TABLE Commands

With the exception of performing a table rename, complete ALTER TABLE statements consecutively. For example, to add multiple columns to a table, issue consecutive ALTER TABLE ADD COLUMN statements.

External Table Restrictions

Not all ALTER TABLE options pertain to external tables. For instance, you cannot add a column to an external table, but you can rename the table:

=> ALTER TABLE mytable RENAME TO mytable2;
ALTER TABLE

Restoring to an Earlier Epoch

If you restore the database to an epoch that precedes changes to the table definition, the restore operation reverts the table to the earlier definition. For example, if you change a column's data type from CHAR(8) to CHAR(16) in epoch 10, and then restore the database from epoch 5, the column reverts to CHAR(8) data type.