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.