How to set a column to UNUSED

Setting a column to UNUSED can be a solution when you cannot or should not attempt to drop a column due to performance reasons.

Syntax:

ALTER TABLE [table name] SET UNUSED (col1, col2, ... col n);

or

ALTER TABLE [table name] SET UNUSED COLUMN (col1);

Example:

ALTER TABLE MYTABLE
SET UNUSED COLUMN MODIFIED_DATE;

Verify that the columns were set to UNUSED:

SELECT * 
FROM USER_UNUSED_COL_TABS;

Result:

TABLE_NAME	COUNT
MYTABLE	1
INVOICES_TMP	1

ALTER TABLE MYTABLE
DROP UNUSED COLUMNS;

The column is now gone:

SELECT * 
FROM USER_UNUSED_COL_TABS;

TABLE_NAME	COUNT
INVOICES_TMP	1

You can also use the keyword CHECKPOINT when dropping the unused columns:

ALTER TABLE MYTABLE
DROP UNUSED COLUMNS CHECKPOINT 1000;

Oracle explains about the CHECKPOINT clause:

"This clause causes a checkpoint to be applied after processing the specified number of rows"
and
"Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo Space."

Note that:
Setting a column to UNUSED will drop constraints and indexes on the column
An UNUSED column cannot be accessed, nor recovered.
You can however, add a new column with the same name as any UNUSED column for the table.

Sources:
Oracle Documentation: Marking columns unused
Oracle Documentation: ALTER TABLE