Oracle Tutorial

What is Oracle
SQL Keywords

Oracle Wildcards

Oracle Wildcards

Oracle Aliases

Oracle Aliases

MySQL Tutorial

MySQL Tutorial

PL/SQL Tutorial

PL/SQL Tutorial

Oracle Interview Questions

Oracle Interview Questions and Answers


Oracle Alter Table Add, Drop and Modify Column

Oracle ALTER TABLE statement is used to add, modify, drop or delete columns in a table. The ALTER TABLE statement is also responsible to perform add and drop various constraints on an existing table.

Oracle Alter Table ADD New Column

To add a new column in existing table, use the following syntax:

Syntax

ALTER TABLE table_name
ADD columnname datatype;

Parameters:

table_name: The name of the table that you want to modify.

columnname: The name of the new column that you want to add to the table.

datatype: The data type of the column (NULL or NOT NULL, etc).

Oracle ALTER TABLE ADD New Column Example

The following Oracle statement Alter existing table and add new "phoneNo" column:

Example

ALTER TABLE tblCustomerLoan
ADD phoneNo INT NOT NULL;

Note:

In the above example, the new column, "phoneNo", is of type INT and is going to hold a integer data.

The NOT NULL is a field attribute and it is used to make sure field should not be NULL.

Oracle Alter Table Add Multiple Columns in the Table

To add multiple new column in existing table, use the following syntax:

Syntax

ALTER TABLE table_name
ADD columnname datatype
[ FIRST | AFTER columnname ],
ADD columnname datatype 
[ FIRST | AFTER columnname ],
.... 
;

Parameters:

table_name: The name of the table that you want to modify.

columnname: The name of the new column that you want to add to the table.

datatype: The data type of the column (NULL or NOT NULL, etc).

Oracle ALTER TABLE ADD New Multiple Columns Example

The following Oracle statement Alter existing table and add new "phoneNo" column:

Example

ALTER TABLE tblCustomerLoan
ADD aadharNo INT NOT NULL
AFTER phoneNo,
ADD panNo INT NOT NULL
AFTER aadharNo;

Note:

In the above example, the new column "aadharNo" is of type INT and is going to hold a integer data and will be add after phoneNo column. The another column "panNo" is of type INT and is going to hold a integer data and will be add after aadharNo column.

The NOT NULL is a field attribute and it is used to make sure field should not be NULL.

Oracle ALTER TABLE MODIFY Column in the Table

To change the data type of the column in existing table, use the following syntax:

Syntax

ALTER TABLE table_name
MODIFY COLUMN columnname datatype;

Parameters:

table_name: The name of the table that you want to modify.

columnname: The name of the existing column that you want to modify to the table.

datatype: The modified data type of the column.

Oracle ALTER TABLE ADD New Multiple Columns Example

The following Oracle statement Alter existing "address" by increasing column size from 150 to 200:

Example

ALTER TABLE tblCustomerLoan
MODIFY COLUMN address VARCHAR(200) NOT NULL;

Note:

In the above example, we increase the "address" column size from 150 to 200 of type VARCHAR and is going to hold a VARCHAR data. Also forcing "address" column not to store NULL value into the column.

Oracle ALTER TABLE DROP Column in Table

To delete/remove column in the existing table, use the following syntax:

Syntax

ALTER TABLE table_name
DROP COLUMN columnname;

Parameters:

table_name: The name of the table that you want to modify.

columnname: The name of the existing column that you want to modify to the table.

Oracle ALTER TABLE DROP Column Example

The following Oracle statement drop/remove existing column "phoneNo" from table:

Example

ALTER TABLE tblCustomerLoan
DROP COLUMN phoneNo;

Note:

In the above example, we drop/delete the "phoneNo" column from table.

Oracle ALTER TABLE RENAME Column in Table

To rename column in the existing table, use the following syntax:

Syntax

ALTER TABLE table_name
CHANGE COLUMN oldcolumnname newcolumnname datatype;

Parameters:

table_name: The name of the table that you want to modify.

oldcolumnname: The name of the existing column that you want to modify to the table.

newcolumnname: The new name of the existing column that you want to modify to the table.

datatype: The modified data type of the column.

Oracle ALTER TABLE RENAME Column Name Example

The following Oracle statement rename existing column "phoneNo" to "cellNo" in table:

Example

ALTER TABLE tblCustomerLoan
CHANGE COLUMN phoneNo cellNo INT;

Note:

In the above example, we rename the existing column "phoneNo" to "cellNo" in table.