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

September 10th, 2021

Every database developer inevitably faces the necessity to perform changes to the data, modify and reorganize them. For these tasks, Oracle will serve excellently. 

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.

This article explores in detail the Oracle ALTER TABLE statement.

There are two main ways to alter tables and add columns in Oracle:

Applying the command directly 

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 statement is used to add, modify, or drop/delete columns in a table.

Adding columns

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

ALTER TABLE table_name
  ADD column_name;

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,
       ...
       );

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

ALTER TABLE customers
ADD customer_name varchar2(45);

Renaming columns

To RENAME 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 customer_name column to customer_first_name:

ALTER TABLE table_name
  RENAME COLUMN customer_name TO customer_first_name;

Modifying columns

To MODIFY A COLUMN in an existing table, the Oracle ALTER TABLE syntax is:

ALTER TABLE customers 
  MODIFY column_name column_type;

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

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

Dropping columns

To DROP A COLUMN in an existing table, the Oracle ALTER TABLE 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 customer_name;

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 here.

Leave a Comment