How to ADD, RENAME, MODIFY, and DROP Column, using the ALTER TABLE Statement in Oracle

September 10th, 2021

In this article, we are going to talk about the ALTER TABLE statement and different ways to use it in Oracle. The syntax provided in this article will work for any version of Oracle as well as with SQL Server, MySQL, and PostgreSQL.

Oracle database offers a cutting-edge experience in storing and retrieving all kinds of related information. It’s available both locally and in cloud environments providing outstanding flexibility and reliability. You can use it to run online transaction processing, data warehousing, and mixed database workloads. A lot of world-famous companies like Netflix, LinkedIn, eBay, etc. have chosen Oracle for their tech stacks.

ALTER TABLE Statement

This article explores in detail the Oracle ALTER TABLE command in Oracle that changes the definition of an existing table.

There are two main ways to use the ALTER TABLE statement in Oracle:

ALTER TABLE Syntax in Oracle

Let’s start with the basics. Any database specialist has to deal with lots of commands. The Oracle ALTER TABLE statement is among the basic ones. 

The Oracle ALTER TABLE syntax is used in all Oracle versions (including 11g) to add, modify, or drop/delete columns in a table.

ALTER TABLE Example

In order to take a better look at how the syntax works, we will create a new table in Oracle using the CREATE TABLE statement:

CREATE TABLE ot.customers(
    person_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
);

We will be using the created table to demonstrate how to add, rename, modify, and delete columns.

ALTER TABLE ADD COLUMN in Oracle

ADD COLUMN is one of the Oracle ALTER TABLE statement variations that is used to add a column if not exist. The default column position in this case will be at the end of the table. Unfortunately, the syntax does not allow adding columns

To ADD A COLUMN in a table, the basic ALTER TABLE syntax is:

ALTER TABLE table_name
  ADD column_name;

Here’s an example with the existing customers table. Let’s add a new column, phone_number:

ALTER TABLE customers
ADD phone_number varchar2(50) NOT NULL;

How to Add Multiple Columns

To ADD MULTIPLE COLUMNS to an existing table, the Oracle ALTER TABLE syntax is as follows:

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

Oracle ALTER TABLE RENAME Column

To change a column name in Oracle, use the ALTER TABLE RENAME COLUMN statement. This works for any Oracle version, including 11g and 12c.

To modify a name of a column in an existing table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name
  RENAME COLUMN old_name TO new_name;

Here’s an example of renaming the first_name column to customer_first_name:

ALTER TABLE table_name
  RENAME COLUMN first_name TO customer_first_name;

Oracle ALTER TABLE MODIFY for Column

To MODIFY a column in an existing table, the Oracle ALTER TABLE syntax is:

ALTER TABLE customers 
  MODIFY column_name column_type;

ALTER TABLE MODIFY allows modifying the constraints such as primary key, column size, type, its default value, length, nullability, etc.

Here is an example of modifying the first_name column. In particular, the change will make the column not accept the NULL values.

ALTER TABLE customers
  MODIFY first_name varchar2(100) NOT NULL;

ALTER TABLE DROP Column

There are cases when you need to remove an existing column from a table. That is where ALTER TABLE DROP comes in handy.

To delete a column in an existing table, the Oracle drop column syntax is:

ALTER TABLE table_name
  DROP COLUMN column_name;

Here is an example of dropping the customer_name column:

ALTER TABLE customers 
  DROP COLUMN phone_number;

How to DROP Multiple Columns

ALTER TABLE DROP statement not only allows removing one column but also dropping multiple columns in Oracle:

ALTER TABLE customers 
  DROP COLUMN (cutomer_first_name, last_name, phone_number);

Using the dedicated IDE–dbForge Studio for Oracle

Nowadays, the most precious human resource is time, and manual coding is among the most time-consuming tasks. Is it really necessary to proceed with scripts manually? 

In fact, there are a lot of helpful multifunctional tools that will do all the routine instead of you. One of the most useful ones is dbForge Studio for Oracle, an Oracle GUI client that allows you to add, modify, and drop table columns visually, i.e. you don’t need to write any code at all.

ALTER TABLE ADD COLUMN in Oracle

So, to modify a table in dbForge Studio for Oracle, right-click a required table in Database Explorer, and select Edit Table.

ALTER TABLE Oracle with dbForge Studio

On the Columns tab, right-click anywhere in the columns list grid and select New Column.

ALTER TABLE Oracle example in dbForge Studio

Next, just type the column name and dbForge Studio will automatically suggest column types for it, but you can also select another type in the Data Types drop-down list of a column. Also, you can specify should the column would accept null values and set the default value for a column.

We have created two new columns in the EMP table, EMAIL and CITY.

Oracle ALTER TABLE RENAME with dbForge Studio

Next, all we have to do is to click Apply Changes. Also, we can click Script Changes and select and take a look at the ALTER TABLE script that we would normally write word by word. You can make some required changes, and apply them by executing the script against the Oracle database or save it for future use.

Oracle ALTER TABLE RENAME column example in dbForge Studio

If you need to create or delete a table, you can refer to the How to create and drop table in Oracle with dbForge article.

Oracle ALTER TABLE RENAME Column

Next, let’s go back to Table Editor and modify the City column to County. All you need to rename a column to type the new name instated of the old one.

ALTER TABLE RENAME column Oracle 12c in dbForge Studio

Here is the generated ALTER TABLE RENAME script.

ALTER TABLE RENAME script in dbForge Studio

To learn how to rename Oracle tables, you can refer to the How to rename table name in Oracle blog post.

Oracle ALTER TABLE MODIFY

Table editor also allows modifying the column properties without manual coding. For example, to make our County column not accept NULL values. For this, we need to just check the Not Null option in Table Editor.

Oracle ALTER TABLE MODIFY in dbForge Studio

Let’s take a glimpse at the script changes again.

Oracle ALTER TABLE MODIFY script in dbForge Studio

ALTER TABLE DROP

Finally, to delete columns from the table in dbForge Studio for Oracle, right-click a column, and select Remove column.

ALTER TABLE DROP in dbForge Studio for Oracle

Here is the ALTER TABLE DROP script for deletion of both columns we created, EMAIL and COUNTY.

ALTER TABLE DROP script in dbForge Studio for Oracle

Conclusion

We have explored two main approaches to altering tables and adding columns in Oracle. Using the Oracle ALTER TABLE column statement is convenient if you need to work closely with the script. However, if you want to boost productivity and make the processes faster and more efficient, you might try compatible multifunctional tools with built-in capabilities like dbForge Studio for Oracle. You can download it from our website.

Useful Links

Comments are closed.