iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
In Oracle, SET UNUSED quickly makes a column inaccessible but does not reclaim the data space it occupies; DROP UNUSED COLUMNS performs the physical cleanup. A virtual column is different: its value is derived from an expression rather than assigned directly. These operations have distinct dependency and recovery implications, so check your Oracle release, table type, and dependent objects before changing a production schema.
What does SET UNUSED do in Oracle?
In Oracle AI Database 26, 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 makes the columns inaccessible. Oracle documents this as faster than dropping the columns.
An unused column is treated as dropped for access: it no longer appears in SELECT * or DESCRIBE, and applications cannot select it. There is no matching SET USED operation to restore it, and the DDL change cannot be rolled back like ordinary transactional DML. You can add a new column with the same name, but the unused column still counts toward the table’s 1,000-column limit until it is physically removed. Oracle documents these behaviors in its Oracle AI Database 26 ALTER TABLE reference.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteDoes SET UNUSED reclaim space?
No. For an internal heap-organized table, SET UNUSED does not remove the stored data or restore the disk space it occupies. It is a way to retire a column from use before carrying out physical cleanup, not a space-reclamation operation. Oracle’s Oracle AI Database 26 Administrator’s Guide describes dropping unused columns as the step that physically removes them and reclaims extra disk space.
#1 Best Overall
How do I drop unused columns in Oracle?
Use DROP UNUSED COLUMNS after you have decided the columns can be permanently removed. Oracle’s Administrator’s Guide illustrates the two-step pattern below:
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 removes the unused columns physically. For long drop operations, Oracle AI Database 26 supports an optional CHECKPOINT clause to limit accumulated undo. A checkpoint is not a guarantee against interruption: check the exact release’s statement semantics and include the operation in your production recovery plan. See the ALTER TABLE reference.
Rank #2
Check dependencies before a direct drop
Dropping a column can affect dependent schema objects. Oracle documents that indexes on target columns are dropped and constraints referencing a target column are removed. Certain constraints that connect a target column to a remaining column or an external column require CASCADE CONSTRAINTS. Review the affected objects and decide how to handle them before running the DDL; do not assume that a direct drop has the same operational impact as marking a column unused.
Recommended Free Tools
Find tables with unused columns
Oracle provides USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS, and DBA_UNUSED_COL_TABS to identify tables containing unused columns. The administrator view includes a COUNT field indicating how many unused columns a table has. Access to a particular view depends on your privileges.
Rank #3
External-table exception
Oracle AI Database 26 documents different behavior for 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.
How SET UNUSED and DROP UNUSED COLUMNS differ
| Consideration | SET UNUSED |
DROP UNUSED COLUMNS |
|---|---|---|
| Purpose and timing | Marks columns inaccessible; Oracle says this is faster than dropping them for an internal heap-organized table. | Physically removes columns that were marked unused. |
| Stored data and space | Leaves column data in the rows; does not reclaim its disk space. | Removes the unused columns and reclaims extra disk space, according to Oracle’s Administrator’s Guide. |
| Access and name | Column cannot be accessed and is omitted from SELECT * and DESCRIBE. Its name can be reused, but it cannot be restored with SET USED. |
Completes removal of the unused column; inspect dependencies and recovery implications before execution. |
| Column limit | Unused columns continue counting toward Oracle’s 1,000-column table limit until physically removed. | Removes the unused columns that would otherwise continue counting toward the limit. |
| Operational planning | Still a DDL change that cannot be rolled back like ordinary transactional DML. | May accumulate undo during a long drop; the optional CHECKPOINT clause can limit accumulated undo, subject to release-specific semantics and recovery planning. |
What is a virtual column in Oracle SQL?
A virtual column gets its value from a defining expression instead of a value assigned directly to that column. Oracle’s Administrator’s Guide says its value is calculated when queried. The detailed restrictions summarized here are from the Oracle Database 12.2 CREATE TABLE reference; confirm support and syntax in the documentation for the release you use.
Expression and table restrictions
In the cited Oracle Database 12.2 reference, virtual columns are supported only in relational heap tables. Their expressions must return scalar values, may reference only columns in the same table, and may not refer to another virtual column by name.
Can you update a virtual column?
No. Oracle’s SQL reference says a virtual column cannot be assigned in an UPDATE SET clause. It can still be used in predicates.
Indexing a virtual column
An index on a virtual column is equivalent to a function-based index. Treat it as a dependent object when assessing changes to the expression or to functions it uses.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.What happens if a function used by a virtual column changes?
Oracle Database 12.2 documents a specific hazard: if a virtual-column expression uses a deterministic PL/SQL function and that function is replaced, Oracle does not automatically invalidate dependent objects. Oracle lists maintenance actions for this case in its virtual-column reference:
- Disable and re-enable constraints on the virtual column.
- Rebuild indexes on the virtual column.
- Fully refresh dependent materialized views.
- Flush the result cache, if applicable.
- Regather table statistics.
This is a documented function-replacement scenario, not a blanket requirement for every virtual-column change. Validate the dependent objects and maintenance steps against the release and schema involved.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Quick Recap
Plan a column change safely
- Confirm the Oracle release and whether the table is an internal heap-organized table or an external table.
- Use
SET UNUSEDwhen quick retirement from access is the immediate goal; do not treat it as space reclamation. - Before physical removal, review indexes, constraints, and other dependencies, including whether
CASCADE CONSTRAINTSis needed. - Plan the drop for the table’s size, undo capacity, and recovery requirements; verify any
CHECKPOINTbehavior in the relevant release documentation. - For virtual columns, verify expression eligibility and account for indexes or other objects that depend on the expression.
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.
