---

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

Note:

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

---

---