Alter Table Command in Oracle SQL

The ALTER TABLE command allows you to rename an existing Oracle table to a new table. The same command can also be used to add, modify, or drop a column from an existing Oracle table.

How to Alter a Table in Oracle?

Syntax to rename a table:

ALTER TABLE <table-name> RENAME TO <new-table-name>;

Example:

Suppose you have a table TB_EMP and you want to rename this table to TB_EMPLOYEE. Following example will help you to do it.

SQL> SELECT * FROM TB_EMP;

        ID NAME

---------- --------------------

         1 AMIT KUMAR

SQL> ALTER TABLE TB_EMP RENAME TO TB_EMPLOYEE;

Table altered.

SQL> SELECT * FROM TB_EMP;

SELECT * FROM TB_EMP

              *

ERROR at line 1:

ORA-00942: table or view does not exist

In the above example, you can see the table TB_EMP does not exist because it has been renamed to TB_EMPLOYEE.

SQL> SELECT * FROM TB_EMPLOYEE;

        ID NAME

---------- --------------------

         1 AMIT KUMAR

How to Add a Column to a Table?

Syntax: Following syntax will help you to add 1 column to an existing table:

ALTER TABLE <table-name> ADD <column-name> <column-definition>;

Example:

SQL> ALTER TABLE TB_EMPLOYEE ADD ADDRESS VARCHAR2(50);

Table altered.

SQL> DESC TB_EMPLOYEE

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------

 ID                                                 NUMBER

 NAME                                               VARCHAR2(20)

Syntax: Following syntax will help you to add multiple columns to an existing table:

ALTER TABLE <table-name> ADD (Column-1 column-definition,

                              Column-2 column-definition,

                              ...

                              Column-n column-definition);

Example:

Using below example, you can add two columns (CITY and STATE) to the TB_EMPLOYEE table.

SQL> ALTER TABLE TB_EMPLOYEE ADD (CITY VARCHAR2(20),

STATE VARCHAR2(20));

Table altered.

How to Modify a Column in an Oracle Table?

Syntax: You can modify a column in an existing table using below syntax:

ALTER TABLE <table-name> MODIFY <column-name> column-type;

Example:

Following command will modify the column NAME with new data type VARCHAR2(25).

SQL> ALTER TABLE TB_EMPLOYEE MODIFY NAME VARCHAR2(25);

Table altered.

Syntax: You can modify multiple column in an existing Table using below syntax:

ALTER TABLE <table-name> MODIFY (Column-1 column-type,

                                 Column-2 column-type,

                                 ...

                                 Column-n column-type);

Example:

Following example will modify NAME and City column as shown:

SQL> ALTER TABLE TB_EMPLOYEE MODIFY (NAME VARCHAR2(30),CITY VARCHAR2(35));

Table altered.

How to Drop a Column in an Oracle Table?

Syntax: You can drop a column in an existing table using below syntax.

ALTER TABLE table-name DROP COLUMN column-name;

Example:

SQL> ALTER TABLE TB_EMPLOYEE DROP COLUMN STATE;

Table altered.

This statement will drop the column called STATE from the table called TB_EMPLOYEE.

How to Rename a Column in an Oracle Table?

Syntax: You can rename a column in an existing table using below syntax:

ALTER TABLE table-name RENAME COLUMN old-name TO new-name;

Example:

SQL> ALTER TABLE TB_EMPLOYEE RENAME COLUMN CITY TO EMP_CITY;

Table altered.

This will rename the column called CITY to new name called EMP_CITY.

Note: RENAME feature is started from Oracle 9i Release 2.