Alter Table Reference

Examples are offered instead of complete explanations for easy copy and paste.

Rename a Table

ALTER TABLE old_name RENAME TO new_name;

Add a Column to a Table

Add one column at a time.

ALTER TABLE TABLE_NAME ADD column_name varchar2(50);

Add multiple columns.

ALTER TABLE TABLE_NAME ADD

(    new_col_1     NUMBER,

     new_col_2     varchar2(20)

);

Remove (Drop) a Column

ALTER TABLE TABLE_NAME DROP COLUMN useless_column;

Rename a Column

ALTER TABLE TABLE_NAME RENAME COLUMN old_col_name TO new_col_name;

Adjust a Column’s Data Type

Adjust one column at a time.

ALTER TABLE TABLE_NAME MODIFY column_name varchar2(10);

ALTER TABLE TABLE_NAME MODIFY column_name varchar2(20);

ALTER TABLE TABLE_NAME MODIFY column_name varchar2(30) NOT NULL;

Adjust multiple columns. This is just like adding columns, only it uses the keyword MODIFY instead of ADD.

ALTER TABLE TABLE_NAME MODIFY

(    adjusted_col_1     varchar2(33),

     adjusted_col_2     varchar2(20) NOT NULL

);