Back to the Basics: Dropping UNUSED columns in Oracle
Posted by decipherinfosys on November 15, 2007
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,
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;
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
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;
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.
Sorry, the comment form is closed at this time.