This SQL tutorial focuses on the Oracle ALTER TABLE statement. This tutorial is the second part of two posts describing DDL (Data Definition Language) statements in Oracle.

In this post you will learn how to modify the columns within the table (such as removing, adding, and changing the data type of a column). The previous post described the concept of creating and deleting tables.


 

Oracle ALTER TABLE – Adding New Column

The syntax to add a column in Oracle is:

ALTER TABLE table_name
ADD column_name column_datatype

For example:

ALTER TABLE products
ADD discount NUMBER(8,2)
  • You cannot define where the new column will be located; each newly inserted column is always located last.
  • If the table already contains columns with values, the new column will be populated with NULL values for all these rows.

Oracle ALTER TABLE – Changing Column Data Type

The syntax for changing a column’s data type in Oracle is:

ALTER TABLE table_name
MODIFY column_name column_datatype

For example:

ALTER TABLE products
ALTER COLUMN discount NUMBER(5)

 

Oracle ALTER TABLE – Dropping a Column

The syntax to remove a column in Oracle is:

ALTER TABLE table_name
DROP COLUMN column_name

For example:

ALTER TABLE products
DROP COLUMN discount