Back to the Basics: Dropping UNUSED columns in Oracle

In our back to basic series today, we will show another approach of dropping columns from the table.  Normally columns are dropped using ‘ALTER TABLE..DROP COLUMN’ syntax.  There is also a DROP UNUSED COLUMN clause, using which we can drop all the unwanted columns from the table. Let us see it by example.

CREATE TABLE TEST
(
TEST_ID       NUMBER NOT NULL,
TEST_NAME     VARCHAR2(128),
STATUS        VARCHAR2(7),
CREATE_DATE   DATE,
MISC_ALPHA1   VARCHAR(30),
MISC_NUM1     NUMBER(9),
MISC_DATE1    DATE,
CONSTRAINT PK_TEST PRIMARY KEY(TEST_ID)
)
/

In above table, we have created three extra columns MISC_ALPHA1, MISC_NUM1 and MISC_DATE1 which we will drop it. In order to use DROP UNUSED COLUMN we have to first set column as unused.

SQL> ALTER TABLE TEST SET UNUSED COLUMN MISC_ALPHA1;

OR

SQL> ALTER TABLE TEST SET UNUSED (MISC_NUM1, MISC_DATE1);

Marking column as unused is a logical approach instead of dropping column physically. Once columns are marked as unused, for all practical purposes, end user cannot see it. SELECT statement will not return the columns marked as unused. Even describe command also ignores the unused columns. Take a look at the result set below.

SQL> desc TEST
Name                                      Null?    Type
—————————————– ——– —————
TEST_ID                                   NOT NULL NUMBER
TEST_NAME                                          VARCHAR2(12
STATUS                                             VARCHAR2(7)
CREATE_DATE                                        DATE

We can also query the USER_UNUSED_COL_TABS view to see which table has how many columns unused.  For our example, we rendered three columns unused.

SQL> SELECT * FROM USER_UNUSED_COL_TABS;

TABLE_NAME                          COUNT
—————————— ———-
TEST                                    3

Unused column retains the data until they are dropped. Also if you have one LONG column marked as unused, you cannot add new column with LONG data type until unused column is dropped but that is not the case with columns with other data type. Oracle will allow you to add new column with the same name as unused column. Run following command to drop all the unused columns from the table.

SQL> ALTER TABLE TEST DROP UNUSED COLUMNS;

Marking column as unused is advantageous in the environment where data set is very large. Dropping column physically may take long time instead marking them as unused is done in no time and columns are not viewable to the outside world. Then later on during down time, we can issue command to drop the columns. If you use ‘DROP COLUMN’ command to drop the column other than the columns marked as ‘unused’, Oracle will also drop the column.