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.

alter table command

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.

1 thought on “Alter Table Command in Oracle SQL”

  1. udhayakumar

    great job !! Great and Clear explanation. I’ve understood all just after first read.

Leave a Comment

Your email address will not be published. Required fields are marked *

shares
Scroll to Top