EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Oracle Tutorial Oracle Alter Table
 

Oracle Alter Table

Priya Pedamkar
Article byPriya Pedamkar

Updated March 28, 2023

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

Oracle Alter Table 1

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.

Oracle Alter Table 2

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.

Oracle Alter Table 3

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.

Modify Multiple Columns

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.

Rename a Column

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.

Rename Table

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.

Drop a Column

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW