Oracle ALTER TABLE MODIFY Column

Summary: in this tutorial, you will learn how to use the Oracle ALTER TABLE MODIFY column statement to change the definition of existing columns.

To change the definition of a column in a table, you use the ALTER TABLE MODIFY column syntax as follows:

ALTER TABLE table_name 
MODIFY column_name action;Code language: SQL (Structured Query Language) (sql)

The statement is straightforward. To modify a column of a table, you need to specify the column name, table name, and action that you want to perform.

Oracle allows you to perform many actions but the following are the main ones:

Modify the column’s visibility
Allow or not allow null values
Shorten or widen the size of the column
Change the default value of a column
Modify the expression of the virtual columns

To modify multiple columns, you use the following syntax:

ALTER TABLE table_name
MODIFY (
    column_name_1 action,
    column_name_2 action,
    ...
);
Code language: SQL (Structured Query Language) (sql)

Oracle ALTER TABLE MODIFY column examples

First, create a new table named accounts for the demonstration:

CREATE TABLE accounts (
    account_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(25) NOT NULL,
    last_name VARCHAR2(25) NOT NULL,
    email VARCHAR2(100),
    phone VARCHAR2(12) ,
    full_name VARCHAR2(51) GENERATED ALWAYS AS( 
            first_name || ' ' || last_name
    ),
    PRIMARY KEY(account_id)
);
Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the accounts table:

INSERT INTO accounts(first_name,last_name,phone)
VALUES('Trinity',
       'Knox',
       '410-555-0197');


INSERT INTO accounts(first_name,last_name,phone)
VALUES('Mellissa',
       'Porter',
       '410-555-0198');


INSERT INTO accounts(first_name,last_name,phone)
VALUES('Leeanna',
       'Bowman',
       '410-555-0199');
Code language: SQL (Structured Query Language) (sql)

Third, verify the insert operation by using the following SELECT statement:

SELECT
    *
FROM
    accounts;
Code language: SQL (Structured Query Language) (sql)

Oracle ALTER TABLE MODIFY Column - example

A) Modify the column’s visibility

In Oracle Database 12c, you can define table columns as invisible or visible. Invisible columns are not available for the query like:

SELECT
    *
FROM
    table_name;
Code language: SQL (Structured Query Language) (sql)

Or statement like:

DESCRIBE table_name;
Code language: SQL (Structured Query Language) (sql)

in SQL*Plus.

However, you can query the invisible columns by specifying them explicitly in the query:

SELECT
    invisible_column_1,
    invisible_column_2
FROM
    table_name;
Code language: SQL (Structured Query Language) (sql)

By default, table columns are visible. You can define an invisible column when you create the table or using ALTER TABLE MODIFY column statement.

For example, the following statement makes the full_name column invisible:

ALTER TABLE accounts 
MODIFY full_name INVISIBLE;
Code language: SQL (Structured Query Language) (sql)

Oracle ALTER TABLE MODIFY Column visibility example

The following statement returns data from all columns of the accounts table except the full_name column:

SELECT
    *
FROM
    accounts; 
Code language: SQL (Structured Query Language) (sql)

This is because the full_name column is invisible.

To change a column from invisible to visible, you use the statement below:

ALTER TABLE accounts 
MODIFY full_name VISIBLE;Code language: SQL (Structured Query Language) (sql)

B) Allow or not allow null example

The following statement changes the email column to accept non-null values:

ALTER TABLE accounts 
MODIFY email VARCHAR2( 100 ) NOT NULL;Code language: SQL (Structured Query Language) (sql)

However, Oracle issued the following error:

SQL Error: ORA-02296: cannot enable (OT.) - null values found
Code language: SQL (Structured Query Language) (sql)

Because when you change a column from nullable to non-nullable, you must ensure that the existing data meets the new constraint.

To fix this, we update the values for the email column first:

UPDATE 
    accounts
SET 
    email = LOWER(first_name || '.' || last_name || '@oracletutorial.com') ;
Code language: SQL (Structured Query Language) (sql)

Note that the LOWER() function converts a string to lowercase.

And then change the column’s constraint:

ALTER TABLE accounts 
MODIFY email VARCHAR2( 100 ) NOT NULL;Code language: SQL (Structured Query Language) (sql)

Now, it is working as expected.

C) Widen or shorten the size of a column example

Suppose, we want to add international code to the phone numbers. Before doing it, we must widen the size of the phone column by using the following statement:

ALTER TABLE accounts 
MODIFY phone VARCHAR2( 15 );Code language: SQL (Structured Query Language) (sql)

Now, we can update the phone numbers:

UPDATE
    accounts
SET
    phone = '+1-' || phone;Code language: SQL (Structured Query Language) (sql)

The following statement verifies the update:

SELECT
    *
FROM
    accounts;
Code language: SQL (Structured Query Language) (sql)

Oracle ALTER TABLE MODIFY column - widen column size example

To shorten the size of a column, you make sure that all data in the column fits the new size.

For example, we try to shorten the size of the phone column down to 12 characters:

ALTER TABLE accounts 
MODIFY phone VARCHAR2( 12 );Code language: SQL (Structured Query Language) (sql)

Oracle Database issued the following error:

SQL Error: ORA-01441: cannot decrease column length because some  value is too big
Code language: SQL (Structured Query Language) (sql)

To fix this, first, we should remove the international code from the phone numbers:

UPDATE
    accounts
SET
    phone = REPLACE(
        phone,
        '+1-',
        ''
    );
Code language: SQL (Structured Query Language) (sql)

The REPLACE() function replaces a substring with a new substring. In this case, it replaces the ‘+1-‘ with an empty string.

And then shorten the size of the phone column:

ALTER TABLE accounts 
MODIFY phone VARCHAR2( 12 );Code language: SQL (Structured Query Language) (sql)

D) Modify virtual column

Suppose, we the full name in the following format:

last_name, first_nameCode language: SQL (Structured Query Language) (sql)

To do this, we can change the expression of the virtual column full_name as follows:

ALTER TABLE accounts 
MODIFY full_name VARCHAR2(52) 
GENERATED ALWAYS AS (last_name || ', ' || first_name);Code language: SQL (Structured Query Language) (sql)

The following statement verifies the modification:

SELECT
    *
FROM
    accounts;
Code language: SQL (Structured Query Language) (sql)

Oracle ALTER TABLE MODIFY Column - modify virtual column example

E) Modify the default value of a column

Let’s add a new column named status to the accounts table with default value 1.

ALTER TABLE accounts
ADD status NUMBER( 1, 0 ) DEFAULT 1 NOT NULL ;
Code language: SQL (Structured Query Language) (sql)

Oracle ALTER TABLE MODIFY Column - Add status column

Once you execute the statement, the values in the status column are set to 1 for all existing rows in the accounts table.

To change the default value of the status column to 0, you use the following statement:

ALTER TABLE accounts 
MODIFY status DEFAULT 0;Code language: SQL (Structured Query Language) (sql)

We can add a new row to the accounts table to check whether the default value of the status column is 0 or 1:

INSERT INTO accounts ( first_name, last_name, email, phone )
VALUES ( 'Julia',
         'Madden',
         'julia.madden@oracletutorial.com',
         '410-555-0200' );
Code language: SQL (Structured Query Language) (sql)

Query data from the accounts table:

SELECT
  *
FROM
  accounts;
Code language: SQL (Structured Query Language) (sql)

Oracle ALTER TABLE MODIFY Column - change column default value

As you can see, the value in the status column for the account with id 4 is 0 as expected.

In this tutorial, you have learned how to use the Oracle ALTER TABLE MODIFY column statement to change the definition of existing columns in a table.

Oracle Drop Column

Summary: in this tutorial, you will learn how to use the Oracle drop column statements to remove one or more columns from a table.

Oracle Drop Column using SET UNUSED COLUMN clause

The process of dropping a column from a big table can be time and resource-consuming. Therefore, we typically drop the column logically by using the ALTER TABLE SET UNUSED COLUMN statement as follows:

ALTER TABLE table_name 
SET UNUSED COLUMN column_name;Code language: SQL (Structured Query Language) (sql)

Once you execute the statement, the column is no longer visible for access.

During the off-peak hours, you can drop the unused columns physically using the following statement:

ALTER TABLE table_name
DROP UNUSED COLUMNS;
Code language: SQL (Structured Query Language) (sql)

If you want to reduce the amount of undo logs accumulated, you can use the CHECKPOINT option that forces a checkpoint after the specified number of rows has been processed.

ALTER TABLE table_name 
DROP UNUSED COLUMNS CHECKPOINT 250;
Code language: SQL (Structured Query Language) (sql)

Oracle SET UNUSED COLUMN example

Let’s create a table named suppliers for the demonstration:

CREATE TABLE suppliers (
    supplier_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    contact_name VARCHAR2(255) NOT NULL,
    company_name VARCHAR2(255),
    phone VARCHAR2(100) NOT NULL,
    email VARCHAR2(255) NOT NULL,
    fax VARCHAR2(100) NOT NULL,
    PRIMARY KEY(supplier_id)
);
Code language: SQL (Structured Query Language) (sql)

The following statements insert sample data into the suppliers table:

INSERT INTO suppliers (contact_name,company_name,phone,email,fax)
VALUES ('Solomon F. Zamora',
        'Elit LLP',
        '1-245-616-6781',
        'enim.condimentum@pellentesqueeget.org',
        '1-593-653-6421');


INSERT INTO suppliers (contact_name,company_name,phone,email,fax)
VALUES ('Haley Franco',
        'Ante Vivamus Limited',
        '1-754-597-2827',
        'Nunc@ac.com',
        '1-167-362-9592');


INSERT INTO suppliers (contact_name,company_name,phone,email,fax)
VALUES ('Gail X. Tyson',
        'Vulputate Velit Eu Inc.',
        '1-331-448-8406',
        'sem@gravidasit.edu',
        '1-886-556-8494');


INSERT INTO suppliers (contact_name,company_name,phone,email,fax)
VALUES ('Alec N. Strickland',
        'In At Associates',
        '1-467-132-4527',
        'Lorem@sedtortor.com',
        '1-735-818-0914');


INSERT INTO suppliers (contact_name,company_name,phone,email,fax)
VALUES ('Britanni Holt',
        'Magna Cras Convallis Corp.',
        '1-842-554-5106',
        'varius@seddictumeleifend.ca',
        '1-381-532-1632');


INSERT INTO suppliers (contact_name,company_name,phone,email,fax)
VALUES ('Audra O. Ingram',
        'Commodo LLP',
        '1-934-490-5667',
        'dictum.augue.malesuada@idmagnaet.net',
        '1-225-217-4699');


INSERT INTO suppliers (contact_name,company_name,phone,email,fax)
VALUES ('Cody K. Chapman',
        'Tempor Arcu Inc.',
        '1-349-383-6623',
        'non.arcu.Vivamus@rutrumnon.co.uk',
        '1-824-229-3521');


INSERT INTO suppliers (contact_name,company_name,phone,email,fax)
VALUES ('Tobias Merritt',
        'Amet Risus Company',
        '1-457-675-2547',
        'felis@ut.net',
        '1-404-101-9940');


INSERT INTO suppliers (contact_name,company_name,phone,email,fax)
VALUES ('Ryder G. Vega',
        'Massa LLC',
        '1-655-465-4319',
        'dui.nec@convalliserateget.co.uk',
        '1-282-381-9477');


INSERT INTO suppliers (contact_name,company_name,phone,email,fax)
VALUES ('Arthur Woods',
        'Donec Elementum Lorem Foundation',
        '1-406-810-9583',
        'eros.turpis.non@anteMaecenasmi.co.uk',
        '1-462-765-8157');


INSERT INTO suppliers (contact_name,company_name,phone,email,fax)
VALUES ('Lael Snider',
        'Ultricies Adipiscing Enim Corporation',
        '1-252-634-4780',
        'natoque.penatibus@in.com',
        '1-986-508-6373');
Code language: SQL (Structured Query Language) (sql)

To logically drop the fax column from the suppliers table, you use the following statement:

ALTER TABLE suppliers 
SET UNUSED COLUMN fax;Code language: SQL (Structured Query Language) (sql)

From now on, you cannot access the fax column anymore:

SELECT
    *
FROM
    suppliers;
Code language: SQL (Structured Query Language) (sql)

You can view the number of unused columns per table from the DBA_UNUSED_COL_TABS view:

SELECT
    *
FROM
    DBA_UNUSED_COL_TABS;
Code language: SQL (Structured Query Language) (sql)

As you can see, the suppliers table has one unused column.

To drop all unused columns from the suppliers table, you use the following statement:

ALTER TABLE suppliers 
DROP UNUSED COLUMNS;
Code language: SQL (Structured Query Language) (sql)

Querying data from the DBA_UNUSED_COL_TABS view again, you will get an empty result set.

Oracle Drop Column using DROP COLUMN clause

To drop a column from a table physically, you use the following statement:

ALTER TABLE table_name 
DROP COLUMN column_name;
Code language: SQL (Structured Query Language) (sql)

To drop multiple columns, you use the statement below:

ALTER TABLE table_name
DROP (
    column_name_1,
    column_name_2
);
Code language: SQL (Structured Query Language) (sql)

Oracle DROP COLUMN clause example

The following statement removes the email and phone columns from the suppliers table:

ALTER TABLE
    suppliers 
DROP (
        email,
        phone
    );
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle drop column statement to delete one or more columns from a table.

Oracle ALTER TABLE MODIFY or DROP Column