EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Oracle Tutorial Oracle Alter Table
Secondary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Golden Gate
    • Oracle Virtual Machine
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle?Primavera
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle ERP
    • Oracle ASM
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Oracle Alter Table

By Priya PedamkarPriya Pedamkar

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

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,112 ratings)

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
Popular Course in this category
Oracle Training (14 Courses, 8+ Projects)
  14 Online Courses |  8 Hands-on Projects |  120+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Oracle DBA Database Management System Training (2 Courses)4.9
All in One Financial Analyst Bundle- 250+ Courses, 40+ Projects4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more