Efficiently Managing Columns in Oracle: Marking and Dropping Unused Columns
There may be situations where you need to drop a column containing a large amount of data, such as 10 million rows. Dropping such a column can take significant time, and Oracle will lock the table during the operation.
Instead of dropping the column directly, you can use the ALTER TABLE ... SET UNUSED command to make the column invisible to users. This allows you to defer the physical deletion of the column until a more convenient time. When marked as unused, the column is stored in the system as “unused.”
Marking a Column as Unused
The following example demonstrates how to mark a column as unused:
SQL> DESC abc_test;
Name Null Type
---------- ---- ------------
NAME VARCHAR2(20)
TOTAL_ROWS NUMBER
SQL> ALTER TABLE abc_test ADD (lname VARCHAR2(20));
-- Output:
-- Table ABC_TEST altered.
SQL> DESC abc_test;
Name Null Type
---------- ---- ------------
NAME VARCHAR2(20)
TOTAL_ROWS NUMBER
LNAME VARCHAR2(20)
SQL> ALTER TABLE abc_test SET UNUSED (lname);
-- Output:
-- Table ABC_TEST altered.
SQL> DESC abc_test;
Name Null Type
---------- ---- ------------
NAME VARCHAR2(20)
TOTAL_ROWS NUMBER
After executing the SET UNUSED command, the column LNAME will no longer be visible to users. However, it remains in the system metadata and can be dropped physically later.
Dropping Unused Columns
If you decide to physically delete the unused columns at a later date, you can do so using the following command:
ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 250;
Note: Dropping unused columns will still place a lock on the base table. It is recommended to perform this operation during a maintenance period to avoid disrupting normal database operations.
Viewing Unused Columns
You can use the DBA_UNUSED_COL_TABS view to check the number of unused columns per table. This helps in identifying tables with marked unused columns for cleanup.
Physically Dropping Columns
To physically drop columns, use one of the following commands depending on whether you wish to drop a single column or multiple columns:
-- Dropping a single column
ALTER TABLE table_name DROP COLUMN column_name;
-- Dropping multiple columns
ALTER TABLE table_name DROP (column_name1, column_name2);
Conclusion
Using ALTER TABLE ... SET UNUSED is a practical way to manage large columns without locking the table for extended periods. This method allows you to remove the column logically and defer its physical deletion to a later, less disruptive time. Always plan to perform physical deletions during maintenance windows to avoid potential locking issues.
Home 