Changes the definition of a table.

Syntax

ALTER TABLE name
  action [, ...]
ALTER TABLE name
  RENAME COLUMN column TO new_column
ALTER TABLE name
  RENAME TO new_name

In the preceding syntax, action can be set to one of the following clauses: 

ADD column type [ column_constraint [ ... ] ]
DROP COLUMN column
ADD table_constraint
DROP CONSTRAINT constraint_name [ CASCADE ]
ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
DISABLE TRIGGER [ trigger_name | ALL | USER ]
OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }

Description

You can execute the ALTER TABLE statement to redefine an existing table. The ALTER TABLE statement supports the following clauses:

  • ADD column type: adds a column to the table. The syntax to use this clause in the ALTER TABLE statement is the same as the syntax to use this clause in the CREATE TABLE statement.
  • DROP COLUMN: deletes a column from the table. Indexes and table constraints that involve the column are also deleted.
  • ADD table_constraint: adds a constraint to the table. The syntax to use this clause in the ALTER TABLE statement is the same as the syntax to use this clause in the CREATE TABLE statement.
  • DROP CONSTRAINT: deletes a constraint from the table. Different constraints can have the same name. If the name that you specify in the clause matches multiple constraints, all constraints that match the specified constraint name are deleted.
  • RENAME: renames the table or a column in the table. You can also use this clause to rename an index, a sequence, or a view. The rename operation has no impact on stored data.

You can execute the ALTER TABLE statement only on tables that you own.

Note When you use the ADD COLUMN clause to add a column, the cell that intersects each existing row and the column is filled with the default value of the column. If a value or an expression is not specified in the DEFAULT clause, the cell that intersects each existing row and the column is filled with a null value. If a value or an expression is specified in the DEFAULT clause, the entire table is rewritten when you add the column. If the table contains a large quantity of data, the rewrite process can require a long period of time to perform. During the rewrite process, the amount of disk space that is occupied is doubled. To add a CHECK or NOT NULL constraint, you can scan the table to verify that the values in the column meet the constraint.

When you use the DROP COLUMN clause to delete a column, the column is not physically deleted. Instead, the column becomes invisible to SQL operations. During subsequent insert and update operations on the table, the cells of the column are filled with null values. After you execute the DROP COLUMN statement, the column is immediately deleted. However, the disk space that is occupied by the table remains unchanged because the disk space occupied by the deleted column is not reclaimed until the existing rows are updated.

You cannot modify the system directory table. For more information about valid parameters, see the CREATE TABLE topic.

Parameters

Parameter Description
name The name of the table that you want to modify. The name can be schema-qualified.
column The name of the column that you want to create or the name of the existing column that you want to rename.
new_column The new name of the existing column.
new_name The new name of the table.
type The data type of the new column.
table_constraint The new constraint for the table.
constraint_name The name of the constraint that you want to delete.
CASCADE Specifies that the objects which depend on the deleted constraints are automatically deleted.

Examples

Add a column of the VARCHAR2 data type to a table.

ALTER TABLE emp ADD address VARCHAR2(30);

Delete a column from a table.

ALTER TABLE emp DROP COLUMN address;

Rename a column.

ALTER TABLE emp RENAME COLUMN address TO city;

Rename a table.

ALTER TABLE emp RENAME TO employee;

Add a CHECK constraint to a table.

ALTER TABLE emp ADD CONSTRAINT sal_chk CHECK (sal > 500);

Delete a CHECK constraint from a table.

ALTER TABLE emp DROP CONSTRAINT sal_chk;