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

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