Oracle ALTER TABLE MODIFY column tutorial with Examples

In this tutorial we will learn how to modify columns in existing tables. In this tutorial, we will explore all the scenarios where you have to use ALTER TABLE MODIFY column statement in Oracle.

So let's start.

We use ALTER TABLE MODIFY column command to change columns in existing tables. Like increasing or decreasing the width of a column, change the datatype of a column(conditions apply, we will discuss these later on), adding a constraint to a column (conditional), encrypting or decrypting a column etc.

One of the most common situations where we use ALTER TABLE MODIFY statement is to increase the width of a column.

Examples

How to increase the width of a column

Suppose we have table by name emp with the following structure

table emp for modify column

As you can see from the above picture, the width of the column EName is 10. Now we want to increase the width of EName column to 50. To do that we will issue the following ALTER TABLE MODIFY command

SQL> ALTER TABLE emp MODIFY Ename Varchar2(50);

Oracle will display the message that the table is altered. Now if we again see the structure of the table by giving describe command you will see the change

oracle table column after increasing width

Next, how to decrease the width of a column

To decrease the size of a column in Oracle table

Similarly, we can also decrease the width of a column provided the length of the largest value in the column should fit within the new column size.

Like for example let's say suppose we have a column by the name JOB in the EMP sample table. Its width is Varchar2(50).

emp table decrease size of a column

Now the width of the largest value in the column is:9. Hey! How did we know that? You can find out the length of the largest value in the column by issuing the following query.

SELECT MAX(LENGTH(job)) FROM emp;

finding maximum length value of column

So, if you want to decrease the size of the column then we can decrease it up to Varchar2(9) not below that.

Here is the query to decrease the width of the column

ALTER TABLE emp MODIFY job VARCHAR2(10);

You must have noticed, the command is same for increasing or decreasing the width of a column.

Modifying more than 1 column in one command.

Yes, it's possible, we can modify multiple columns in one command by giving the following SQL statement

ALTER TABLE emp MODIFY (ename VARCHAR2(40), job VARCHAR2(30));

The above command will decrease the width of ename column to 40 and increase job column to 30.

Changing the DATATYPE of a column in Oracle

We can change the datatype of a column to a similar other datatype like for example

DATE to TIMESTAMP
TIMESTAMP to DATE
CHAR to VARCHAR2
VARCHAR2 to CHAR

anytime. whether the table is empty or not empty. But to change the datatype of a column to an entirely different type, like for example VARCHAR2 to NUMBER or DATE to VARCHAR2 then, the table must be empty i.e. no rows should be there. OR at least the column must be empty. If we give the ALTER command then Oracle will through the error

ORA-01439 - column to be modified must be empty to change datatype

Changing DATE column to TIMESTAMP

To change the column datatype from DATE to TIMESTAMP give the following command.

ALTER TABLE emp MODIFY hiredate TIMESTAMP;

modify date to timestamp

Change the datatype from NUMBER to VARCHAR2

We can change the datatype from NUMBER to VARCHAR2 only if the column is empty. Otherwise we will get the following error

ALTER TABLE emp MODIFY mgr VARCHAR2(10)

cannot modify column datatype if column is not empty

Making a column INVISIBLE or VISIBLE (12C)

In 12c we can make a column invisible i.e. the column will be there as it is in the table but, it will not be shown in full table queries. like for example SELECT * FROM emp;

To make a column INVISIBLE we have to use ALTER TABLE MODIFY command like as shown below

ALTER TABLE emp MODIFY city INVISIBLE;

Similarly, to make a column VISIBLE again, give the following command

ALTER TABLE emp MODIFY city VISIBLE;