Free tools Windows power users keep installed
One-click scans. No signup required.
In Oracle, SET UNUSED quickly makes a column inaccessible but does not reclaim its stored space; DROP UNUSED COLUMNS performs the later physical cleanup. A virtual column is different: Oracle derives its value from an expression rather than letting an application assign it directly. The details below follow the Oracle AI Database 26 column-maintenance references and, for virtual-column restrictions, the Oracle Database 12.2 SQL reference.
What does SET UNUSED do in Oracle?
ALTER TABLE ... SET UNUSED marks one or more columns as unused. For an internal heap-organized table, Oracle leaves the column data in the rows but treats the columns as dropped for access. They no longer appear in SELECT * or DESCRIBE, and applications cannot select them. Oracle documents this as faster than dropping the columns outright. See the Oracle AI Database 26 ALTER TABLE reference.
As an Amazon Associate I earn from qualifying purchases.
This is a staging operation, not a reversible rename or a temporary hiding mechanism. There is no matching SET USED operation, and the DDL change cannot be rolled back like ordinary transactional DML. The old column name may be used for a new column, but until the unused column is physically removed it still counts toward Oracle’s 1,000-column table limit.
Does SET UNUSED reclaim space?
No. On an internal heap-organized table, SET UNUSED leaves the old column data in place and does not restore disk space. Use it when making the column inaccessible promptly is useful and physical removal can be scheduled separately. Oracle’s Oracle AI Database 26 Administrator’s Guide describes the later removal as the step that physically deletes unused columns and reclaims the extra disk space.
#1 Best Overall
There is an important table-type exception: Oracle says that on external tables, SET UNUSED is transparently converted to DROP COLUMN. External-table operations are metadata-only, so do not assume the internal heap-table behavior applies unchanged.
How do I drop unused columns in Oracle?
After marking columns unused, remove them with ALTER TABLE ... DROP UNUSED COLUMNS. Oracle’s guide uses this two-step pattern:
Rank #2
ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);
ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS;The first statement makes the named columns inaccessible; the second is the cleanup that physically removes unused columns. The example is illustrative: verify the actual table, column names, table type, and Oracle release before running DDL.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Check the impact before cleanup
For unused-column discovery, Oracle provides the USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS, and DBA_UNUSED_COL_TABS dictionary views. For example, administrators can inspect DBA_UNUSED_COL_TABS and its COUNT field to see the number of unused columns recorded for a table.
Rank #3
Dropping columns can affect related schema objects. Oracle documents that indexes on target columns are dropped and constraints that reference a target column are removed. Some constraints that cross from a target column to a remaining or external column require the CASCADE CONSTRAINTS clause. Review dependencies before issuing the DDL; do not assume a direct drop leaves every related object intact. See the Oracle AI Database 26 ALTER TABLE reference.
Plan for the operation’s resources and recovery
For a long drop operation, the SQL reference documents optional CHECKPOINT behavior to limit accumulated undo. It is not a general guarantee that an operation can be interrupted safely. Assess the table’s size, undo capacity, and production recovery plan using the exact release’s statement semantics before applying it. Oracle does not publish a universal runtime or space-saving figure for these operations.
What is a virtual column in Oracle SQL?
A virtual column gets its value from a defining expression rather than a value assigned directly by an insert or update. Oracle’s Administrator’s Guide says the value is calculated when queried. Unlike an ordinary stored column, it cannot be assigned in an UPDATE statement’s SET clause, although it can be used in predicates.
In the Oracle Database 12.2 SQL reference, virtual columns are supported only on relational heap tables. Their expressions must return a scalar value, may reference columns only in the same table, and may not refer to another virtual column by name. Check the documentation for your target release before relying on those 12.2-specific eligibility details. The Oracle Database 12.2 CREATE TABLE reference describes these restrictions.
Indexing a virtual column
An index on a virtual column is equivalent to a function-based index. That makes the expression and its dependencies operationally relevant, not just a display convenience.
When a deterministic function changes
Oracle documents a specific hazard for a virtual-column expression that uses a deterministic PL/SQL function: replacing that function does not automatically invalidate dependent objects. The Oracle Database 12.2 reference lists maintenance steps for this case: disable and re-enable constraints on the virtual column, rebuild its indexes, fully refresh dependent materialized views, flush the result cache if applicable, and regather table statistics. These are documented steps for this function-replacement scenario, not a blanket requirement for every virtual-column change. See the Oracle Database 12.2 CREATE TABLE reference.
Quick Recap
Choosing between the column-maintenance options
| Operation | Immediate effect | Space reclamation | Access and naming | Operational consideration |
|---|---|---|---|---|
SET UNUSED |
Faster staging step for internal heap-organized tables; data remains in rows. | No immediate reclamation. | Column is inaccessible and omitted from SELECT * and DESCRIBE; its name can be reused. It still counts toward the 1,000-column limit until removed. |
Cannot be reversed with SET USED; external tables instead convert the operation to DROP COLUMN. (Oracle AI Database 26 ALTER TABLE reference) |
DROP UNUSED COLUMNS |
Physically removes columns previously marked unused. | Reclaims extra disk space, according to Oracle’s Administrator’s Guide. | Removed columns are no longer part of the table. | Review index and constraint effects; consider documented CHECKPOINT behavior for long drops and plan undo and recovery. (Oracle AI Database 26 ALTER TABLE reference and Administrator’s Guide) |
| Virtual column | Value is derived from an expression when queried. | Not an unused-column cleanup operation; the cited reference describes expression-based values. | Cannot be directly assigned in an UPDATE SET clause; can be used in predicates. |
Expression and table eligibility are restricted in the cited 12.2 reference; indexing is equivalent to a function-based index. (Oracle Database 12.2 CREATE TABLE reference) |
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Recommended Free Tools
