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 #

First, create a table named suppliers:

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)

Second, insert 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',
        '[email protected]',
        '1-593-653-6421');


INSERT INTO suppliers (contact_name,company_name,phone,email,fax)
VALUES ('Haley Franco',
        'Ante Vivamus Limited',
        '1-754-597-2827',
        '[email protected]',
        '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',
        '[email protected]',
        '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',
        '[email protected]',
        '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',
        '[email protected]',
        '1-381-532-1632');


INSERT INTO suppliers (contact_name,company_name,phone,email,fax)
VALUES ('Audra O. Ingram',
        'Commodo LLP',
        '1-934-490-5667',
        '[email protected]',
        '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',
        '[email protected]',
        '1-824-229-3521');


INSERT INTO suppliers (contact_name,company_name,phone,email,fax)
VALUES ('Tobias Merritt',
        'Amet Risus Company',
        '1-457-675-2547',
        '[email protected]',
        '1-404-101-9940');


INSERT INTO suppliers (contact_name,company_name,phone,email,fax)
VALUES ('Ryder G. Vega',
        'Massa LLC',
        '1-655-465-4319',
        '[email protected]',
        '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',
        '[email protected]',
        '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',
        '[email protected]',
        '1-986-508-6373');
Code language: SQL (Structured Query Language) (sql)

Third, drop the fax column from the suppliers table logically:

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 by using the following statement.

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

The output indicates that the suppliers table has one unused column.

Fourth, drop all unused columns from the suppliers table:

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

Finally, verify the column removal by querying data from the DBA_UNUSED_COL_TABS view again:

SELECT * FROM DBA_UNUSED_COL_TABS;

It returns an empty result set indicating that the we have successfully dropped the unused column.

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)

In this syntax:

• First, specify the name of the table in the ALTER TABLE clause.
• Second, provide the column name you want to drop in the DROP COLUMN clause.

To drop multiple columns at once, you use the following statement:

ALTER TABLE table_name
DROP (
    column1,
    column2,
    column3
);Code language: SQL (Structured Query Language) (sql)

For example, the following statement drops the email and phone columns from the suppliers table:

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

Summary #

• Use the ALTER TABLE SET UNUSED COLUMN and ALTER TABLE DROP UNUSED COLUMNS statements to drop a column from the a table.
• Use the ALTER TABLE DROP COLUMN statement to drop one or more columns physically.

Quiz #