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.

SiteGround Black Friday Sale Below Banner

Get Free Blogging Tips & Technology updates in your Email !!!

Subscribe via RSS feed

The following two tabs change content below.
Anil Kumar
IT Blogger Tips focus on Blogging Tips, SEO Tips, Social Media, SQL Tips, PL/SQL Tips, Oracle DBA, Linux/Unix, Latest Technology, How Tos and Technical Solutions. You can find us on Facebook | Twitter |
Find on Google+

  • udhayakumar

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

Siteground SALE
70% OFF SiteGround WordPress Hosting – $2.95/month (Exclusive Offer !!!)