Oracle Alter Table

Introduction to Oracle Alter Table

In this article, we are going to discuss the Alter table command. This ALTER TABLE statement is used to modify a column, add a column, rename a column, rename a table or delete a column from the table. ALTER TABLE statement comes under DDL (Data Definition Language) queries because it is used to define the structure of the database or make changes to that structure. Hence the knowledge of ALTER TABLE statement is very much required to be a SQL developer. In this article, we are going to discuss in detail with examples. In this topic, we are going to learn about Oracle Alter Table.

Syntax

The syntax of the ALTER TABLE is given below.

ALTER TABLE table_name
Action column_name;

Parameters

• table_name: The name of the table on which we want to apply.
• Action: It represents whether we want to ADD, MODIFY, DROP or RENAME
• column_name: The name of the column on which we want to apply the action.

Examples of Oracle Alter Table

We are now going to discuss the different operations on which we can use the ALTER TABLE statement with examples.

1. Add a Column Using Alter Table Statement

ALTER TABLE statement can be used to add a new column in the existing table. This will be a DDL query since we are using it to change the structure of a table. We are going to use an example for this, In our case, we will add a new column to an existing table employee. The column will be named as PHONE_NUM and it will have a data type as NUMBER with a maximum size of 10. Let us look at the query for the same.

Code:

ALTER TABLE EMPLOYEE
ADD PHONE_NUM NUMBER(10);

Now we will run this query in the SQL Developer and check its output. The below screenshot shows us the output in SQL Developer after executing the query.

As you can see the output “Table Employee altered”. So, we now have an extra column with NULL values.

2. Adding Multiple Columns Using Alter Tables

In this case, we will have to add commas after every column so that Oracle is able to distinguish between two columns. In this example, we are going to add two columns in the orders table. The columns are CUSTOMER_NAME and PAYMENT. Both the columns will have data types as VARCHAR2 with size (20). Let us prepare the query for this case.

Code:

ALTER TABLE orders
ADD (CUSTOMER_NAME varchar2(20),
PAYMENT varchar2(20));

Let us run the query in SQL Developer. The below screenshot shows the output after the execution of the query in the tool.

As you can see our query successfully altered the table ORDERS.

3. Modify Columns Using Alter Table Statement

Just as we can add columns we can also modify existing columns present in a table. It will also be a DDL query since we are modifying the structure of a table. In this, we will change the size of the data type of the column CUSTOMER_NAME of the orders table. For doing that we will have to create a modified query. So, we will now write a query to change the data type size using ALTER TABLE.

Code:

ALTER TABLE orders
MODIFY customer_name varchar2(15);

If you see this query we have reduced the size of the column to 15 from 20 characters. Let us run this query on SQL developer. The below screenshot shows the output of the query.

As you can see that the Script output shows that the table has been successfully altered.

4. Modify Multiple Columns Using Alter Statement

Just as we could add multiple columns in Oracle we can also modify multiple columns using MODIFY with ALTER TABLE statement. In the previous example, we used MODIFY to change the data type size for one column of a table. In this example, we will again use  MODIFY but this time our query will modify two columns of orders table. The columns are ORDER_NAME and PAYMENT. Like in the previous example, we are going to write a query for the same.

Code:

ALTER TABLE orders
MODIFY (CUSTOMER_NAME varchar2(10),
PAYMENT varchar2(10));

So, we have modified the size for both columns to 10. Let us now run the query in SQL developer to check the output. The below screenshot shows the output of the query in SQL developer.

So, as per the output shown above both, the columns have been altered.

5. Rename a Column Using Alter Table

We can use the RENAME keyword with the ALTER TABLE statement to rename a column of a table. It will again be a DDL query as we are altering the table structure. In this example, we will rename the column PAYMENT of orders table to PAYMENT_MODE. Let us now write the query for the same.

Code:

ALTER TABLE orders
RENAME column PAYMENT to PAYMENT_MODE;

Let us now run the query in the sql developer. The below screenshot shows us the output of the query.

As we can see that the column has been successfully renamed.

6. Rename Table Using Alter Table

Just as we renamed a column of a table, we can rename a table itself. In this example, we are going to rename the table orders to shoppers. Let us now see the query for the same.

Code:

ALTER TABLE orders
RENAME TO shoppers;

The below screenshot shows the output in SQL developer.

As per the output, the table is successfully altered.

7. Drop a Column Using Alter Table

Just as in the previous examples we added columns we can also similarly DROP them from the table. In this example, the column PAYMENT_MODE of shoppers table will be dropped. Let us look at the query below.

Code:

ALTER TABLE shoppers
DROP column PAYMENT_MODE;

The below screenshot shows the output of the query in SQL developer.

As we can see the column has been dropped from the shopper’s table.

Recommended Articles

This is a guide to Oracle Alter Table. Here we discuss the ALTER TABLE command in the oracle database along with the various types of actions and respective examples. You may also have a look at the following articles to learn more –

1. Oracle Operators
2. Table in Oracle
3. What is Oracle?
4. Oracle Versions