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 theALTER TABLE SET UNUSED COLUMN statement as follows:

ALTERTABLE table_nameSETUNUSEDCOLUMN 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:

ALTERTABLE table_nameDROPUNUSEDCOLUMNS;Code language:SQL (Structured Query Language)(sql)

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

ALTERTABLE table_nameDROPUNUSEDCOLUMNS CHECKPOINT250;Code language:SQL (Structured Query Language)(sql)

Oracle SET UNUSED COLUMN example#

First,create a table namedsuppliers:

CREATETABLE suppliers (    supplier_idNUMBERGENERATEDBYDEFAULTASIDENTITY,    contact_nameVARCHAR2(255)NOTNULL,    company_nameVARCHAR2(255),    phoneVARCHAR2(100)NOTNULL,    emailVARCHAR2(255)NOTNULL,    faxVARCHAR2(100)NOTNULL,    PRIMARYKEY(supplier_id));Code language:SQL (Structured Query Language)(sql)

Second,insert data into thesuppliers table:

INSERTINTO suppliers (contact_name,company_name,phone,email,fax)VALUES ('Solomon F. Zamora','Elit LLP','1-245-616-6781','[email protected]','1-593-653-6421');INSERTINTO suppliers (contact_name,company_name,phone,email,fax)VALUES ('Haley Franco','Ante Vivamus Limited','1-754-597-2827','[email protected]','1-167-362-9592');INSERTINTO 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');INSERTINTO 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');INSERTINTO 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');INSERTINTO suppliers (contact_name,company_name,phone,email,fax)VALUES ('Audra O. Ingram','Commodo LLP','1-934-490-5667','[email protected]','1-225-217-4699');INSERTINTO 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');INSERTINTO suppliers (contact_name,company_name,phone,email,fax)VALUES ('Tobias Merritt','Amet Risus Company','1-457-675-2547','[email protected]','1-404-101-9940');INSERTINTO suppliers (contact_name,company_name,phone,email,fax)VALUES ('Ryder G. Vega','Massa LLC','1-655-465-4319','[email protected]','1-282-381-9477');INSERTINTO 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');INSERTINTO 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 thefax column from thesuppliers table logically:

ALTERTABLE suppliersSETUNUSEDCOLUMN fax;Code language:SQL (Structured Query Language)(sql)

From now on, you cannot access thefax column anymore:

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

You can view the number of unused columns per table from theDBA_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 thesuppliers table has one unused column.

Fourth, drop all unused columns from thesuppliers table:

ALTERTABLE suppliersDROPUNUSEDCOLUMNS;Code language:SQL (Structured Query Language)(sql)

Finally, verify the column removal by querying data from theDBA_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:

ALTERTABLE table_nameDROPCOLUMN column_name;Code language:SQL (Structured Query Language)(sql)

In this syntax:

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

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

ALTERTABLE table_nameDROP (    column1,    column2,    column3);Code language:SQL (Structured Query Language)(sql)

For example, the following statement drops theemail andphone columns from thesuppliers table:

ALTERTABLE suppliersDROP( email, phone );Code language:SQL (Structured Query Language)(sql)

Summary#

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

Quiz#