Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Oracle
ALTER TABLE abc SET UNUSED COLUMN abccolumn
with this query we can set an "unused column"
but can we reused this column?
please answers
if not so whats diff between "drop column" and "unused column"?
and why we do unused rather than drop ?
Posted 23-Feb-13 23:47pm
saimm665
Edited 24-Feb-13 6:34am
v2
Comments
Sandeep Mewara at 24-Feb-13 10:16am
   
This is not a well framed question! We cannot work out what you are trying to do/ask from the post. Please elaborate and be specific.
Use the "Improve question" link to edit your question and provide better information.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Basically you set a columns to unused if you are concerned about the time it would take to actually drop the column. The column is treaded as not being present. It will not show up in queries and the name off to column can be used to create a new column.
The only action you can perform on an unused column is to actually remove it, there is no way to reactivate it.
 
The difference between DROP COLUMN and SET UNUSED is that the first actually removed the column and performs any clean-up action needed and the second just marks it for disposal.
 
Marking Columns Unused
 
If you are concerned about the length of time it could take to drop column data from all of the rows in a large table, you can use the ALTER TABLE...SET UNUSED statement. This statement marks one or more columns as unused, but does not actually remove the target column data or restore the disk space occupied by these columns. However, a column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. All constraints, indexes, and statistics defined on the column are also removed.
 
To mark the hiredate and mgr columns as unused, execute the following statement:
ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);
You can later remove columns that are marked as unused by issuing an ALTER TABLE...DROP UNUSED COLUMNS statement. Unused columns are also removed from the target table whenever an explicit drop of any particular column or columns of the table is issued.
 
The data dictionary views USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS, or DBA_UNUSED_COL_TABS can be used to list all tables containing unused columns. The COUNT field shows the number of unused columns in the table.
SELECT * FROM DBA_UNUSED_COL_TABS;
OWNER                       TABLE_NAME                  COUNT
--------------------------- --------------------------- -----
HR                          ADMIN_EMP                       2
For external tables, the SET UNUSED statement is transparently converted into an ALTER TABLE DROP COLUMN statement. Because external tables consist of metadata only in the database, the DROP COLUMN statement performs equivalently to the SET UNUSED statement.
Removing Unused Columns
 
The ALTER TABLE...DROP UNUSED COLUMNS statement is the only action allowed on unused columns. It physically removes unused columns from the table and reclaims disk space.
 
In the ALTER TABLE statement that follows, the optional clause CHECKPOINT is specified. This clause causes a checkpoint to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space.
ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;
  Permalink  
Comments
saimm at 25-Feb-13 13:32pm
   
ok thnx andre, nice explanation

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 420
1 CHill60 410
2 Sergey Alexandrovich Kryukov 384
3 PIEBALDconsult 245
4 BillWoodruff 243


Advertise | Privacy | Mobile
Web02 | 2.8.141015.1 | Last Updated 24 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100