Oracle ALTER TABLE

ALTER TABLE statement is used to modify the structure of table in Oracle Database.

Syntax:

The syntax of Oracle ALTER TABLE statement is as follows:

ALTER TABLE table_name alteration;

Here,

table_name – It is the name of table that we want to alter.

alteration – It is here we specify what sort of modification we want to do to the table structure. Some of the alterations that we can do are: Add, Drop or Rename columns. We can also make changes to column definition or rename the table itself.

Let’s see some examples to understand this better:

ORACLE ALTER TABLE Examples

We will be using the Interns Table that we created earlier for this example:

1. Oracle ALTER TABLE ADD column example

This is the syntax for adding a new column to the table:

ALTER TABLE table_name
ADD column_name data_type constraint;

Suppose, we want to add End_date column to the intern table to mention the last day of their service period:

ALTER TABLE interns
ADD end_date DATE;

Let’s view the Interns table using DESC query to see if the End_date column has been appended to the Interns table or not:

We can also add multiple columns to the table by adding multiple new columns in the parenthesis. Let’s have a look at the syntax:

ALTER TABLE table_name
ADD (
    column_1 data_type constraint,
    column_2 data_type constraint,
    ...
);

Let’s look at a practical example:

ALTER TABLE persons 
ADD (
    job_role VARCHAR(10),
    dept_no NUMBER (10)
);

Result:

As we can see, two new columns have been added to the table.

2. Oracle ALTER TABLE MODIFY column example

We can also modify attributes of column by using ALTER TABLE MODIFY statement. Let’s have a look at the syntax:

ALTER TABLE table_name
  MODIFY column1 data_type constraint;

Let’s look at a practical SQL query for changing the attributes of existing column:

ALTER TABLE interns MODIFY location_name NULL;

Result:

As we can see that the location_name column become nullable, which means that we can now assign NULL values to location for interns who work remotely.

Similarly, for multiple columns, the syntax is as follows:

ALTER TABLE table_name
  MODIFY ( column2 data_type constraint,
          column2 data_type constraint,
         ...);

Let’s see one example:

ALTER TABLE interns MODIFY(
    job_role VARCHAR(10) NOT NULL,
    dept_no NUMBER (10) NOT NULL
);

Result:

As we can see, that we managed to alter the attributes of columns job_role and dept_no.

3. Oracle ALTER TABLE DROP COLUMN example

ALTER TABLE DROP is used for deleting an existing column in the table. Here’s the syntax

ALTER TABLE table_name
DROP COLUMN column_name;

Let’s remove some of the columns from Interns Table that we don’t need:

ALTER TABLE interns
DROP
  COLUMN end_date;

Result:

End_date Column has been dropped from the Interns Table.

For dropping multiple columns at the same time, we can use the following syntax.

ALTER TABLE table_name 
DROP (column1,column2,...);

Let’s use this to drop some columns from the Interns Table:

ALTER TABLE interns
DROP
  ( job_role, dept_no );

Result:

As we can see, two columns,i.e, job_role and dept_no have been dropped.

4. Oracle ALTER TABLE RENAME Column example

For giving a new name to column, we use the following example:

ALTER TABLE table_name
RENAME COLUMN column_name_old TO column_name_new;

Let’s see one example, where we rename column location_name to location_site

ALTER TABLE intern 
RENAME COLUMN location_name TO location_site;

Result:

As we can see, location_name column has been renamed to location_site

5. Oracle ALTER TABLE RENAME table example

ALTER TABLE RENAME statement is used for renaming the entire table in Oracle database:

ALTER TABLE table_name
RENAME TO table_name_new;

Let’s see the practical example by renaming table INTERNS:

ALTER TABLE interns RENAME TO juniors;

Result:

Table Intern has been renamed.

In this article, we learned how to use Oracle ALTER TABLE statement to rename the existing table.