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;
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;
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;
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)
);
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');
To logically drop the fax
column from the suppliers
table, you use the following statement:
ALTER TABLE suppliers
SET UNUSED COLUMN fax;
From now on, you cannot access the fax
column anymore:
SELECT
*
FROM
suppliers;

You can view the number of unused columns per table from the DBA_UNUSED_COL_TABS
view:
SELECT
*
FROM
DBA_UNUSED_COL_TABS;

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;
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;
To drop multiple columns, you use the statement below:
ALTER TABLE table_name
DROP (
column_name_1,
column_name_2
);
Oracle DROP COLUMN
clause example
The following statement removes the email
and phone
columns from the suppliers
table:
ALTER TABLE
suppliers
DROP (
email,
phone
);
In this tutorial, you have learned how to use the Oracle drop column statement to delete one or more columns from a table.