How to add column using ALTER TABLE command in Oracle?




Oracle ALTER TABLE ADD COLUMN syntax

To add a new column to an existing table, the syntax for ALTER TABLE ... ADD ...command in Oracle is quite similar to the other databases:

ALTER TABLE table_name 
ADD column_name column_definition;

The new column is NULL unless you define a default value for it.

In MySQL, there is a FIRST|AFTER existing_column clause which defines the position of the new column, but Oracle doesn’t allow that. The ALTER TABLE command above adds the new column as the last column in the table.

To add multiple column to an existing table within one command:

ALTER TABLE table_name
 ADD (column_1 column_definition,
    column_2 column_definition,
    ...
    column_n column_definition)
;

Example

To add new column salary and hire_date to the table employees:

ALTER TABLE employees
ADD (
 hire_date DATE,
 salary NUMBER(10)
);

---

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download for iOS