ocp-047-169

UnUseD
Instead of dropping a table column you are no longer using, you may elect to declare 
it unusedand leave it in place. Once you set a column as UNUSED, it is never 
again available; it is as though it has been dropped. As with dropped columns, any 
constraints or indices on the column will also be dropped. You will never be able to 
recover a column that is set to UNUSED. A ROLLBACK statement will have no 
effect—an UNUSED column will not be recovered in a ROLLBACK operation. 
Once a column is set to UNUSED, you can add new columns that have the same 
name as any unused columns for the table.
So why wouldn’t you just DROP a column instead of setting it to UNUSED? 
One reason is the performance for the DROP 
statement versus the SET UNUSED approach. 
If you’re working with a very large table or set 
of tables, and you need to drop some columns, 
you may find that the system performance 
for executing the DROP is temporarily 
unacceptable, particularly for a system that 
is in heavy production. If this is an issue, and 
you need to achieve the look-and-feel of a dropped column immediately, then the 
SET UNUSED alternative is a welcome option. The performance is speedy, the 

results are—for all practical purposes—the same, and you can always schedule a 

time later to come back and drop the column during a period of low activity in 
the database.
One thing to keep in mind: there’s a limit to the total number of columns 
any given table can have. That limit is 1,000—you cannot create more than a 
thousand columns in any one table. If you set a column to be UNUSED, that 
column will still count as part of the thousand columns toward your limit, until 
you eventually DROP the column—which you can do; we’ll discuss how to drop 
an unused column in a bit.
The syntax for SET UNUSED is virtually identical to the ALTER TABLE . . . 
DROP syntax. Simply replace DROP with the keywords SET UNUSED, and the 
rest is the same. For example:
ALTER TABLE ORDER_RETURNS 
SET UNUSED COLUMN CRUISE_ORDER_DATE;
As with DROP, the syntax for changing multiple columns to the UNUSED state 
requires parentheses and eliminates the COLUMN reserved word, like so:
ALTER TABLE ORDER_RETURNS 
SET UNUSED (CRUISE_ORDER_DATE, FORM_TYPE, NAME_SUFFIX);
You can set as many columns to UNUSED as you wish. The only requirement is 
that you must, as you might guess, satisfy all constraints and other requirements of 
a table and its structure—for example, the table still must have at least one valid 
column at any time—so you cannot set all of its columns to UNUSED.
Tables that have any columns that are set to UNUSED can be found in the data 
dictionary view USER_UNUSED_COL_TABS. However, this view doesn’t reveal 
any column names that are unused; it simply gives you the names of any and all 
tables that contain unused columns, and a numeric count of how many unused 
columns each one contains. You cannot recover the unused columns, nor can you 
even identify them. But you can drop them. To drop those unused columns, use 
this statement:
ALTER TABLE table_nameDROP UNUSED COLUMNS;
For example:
ALTER TABLE ORDER_RETURNS DROP UNUSED COLUMNS;
This statement will drop all unused columns that are associated with the table 
ORDER_RETURNS.