Simply, to drop column from a table, we need to use following statement
ALTER TABLE table_name DROP COLUMN column_name;
Many time, if we use above statement to drop column from a big table, it 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 all 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;
To drop multiple columns, you use the statement below:
ALTER TABLE table_name DROP ( column_name_1, column_name_2 );