对SET UNUSED Clause本质的理解（以及 SET UNUSED Clause和 DROP（column） clause的关系 ）

Syntax

alter_table::=

column_clauses::=

Description of the illustration column_clauses.gif

drop_column_clause ::=
Description of the illustration drop_column_clause.gif

drop_column_clause

Thedrop_column_clause lets you free space in the database by dropping columns you no longer need or by marking them（通过使用set unused从句） to be dropped at a future time when the demand on system resources is less.

• If you drop a nested table column, then its storage table is removed.

• If you drop a LOB column, then the LOB data and its corresponding LOB index segment are removed.

• If you drop a BFILE column, then only the locators stored in that column are removed, not the files referenced by the locators.

• If you drop or mark unused a column defined as anINCLUDING column, then the column stored immediately before this column will become the newINCLUDING column.

注释：

Thedrop_column_clause lets you free space in the database，即drop_column_clause可以释放回收已经分配的空间，也即可以降低表段的高水位线。而在操作级别是表这个对象级别上时，drop table语句则不能降低表段的高水位线，truncate table才可以。

SET UNUSED Clause

Specify SET UNUSED to mark one or more columns as unused.

For an internal heap-organized table,specifying this clause does not actually remove the target columns from each row in the table. It does not restore（回收） the disk space used by these columns.Therefore, the response time is faster than when you execute theDROP （ column） clause.

When you specify this clause for a column in an external table, the clause is transparently converted to anALTERTABLE ... DROP COLUMN statement. The reason for this is that any operation on an external table is a metadata-only operation, so there is no difference in the performance of the two commands.

You can view all tables with columns marked UNUSED in the data dictionary views USER_UNUSED_COL_TABS, DBA_UNUSED_COL_TABS, and ALL_UNUSED_COL_TABS.

See Also:

Oracle Database Reference for information on the data dictionary views

Unused columns are treated as if（好像） they were dropped【译文：Unused的列可以当做它们已经被删除了来看待】, even though their column data remains in the table rows.

After a column has been marked UNUSED, you have no access to that column. ASELECT* query will not retrieve data from unused columns. In addition, the names and types of columns markedUNUSED will not be displayed during aDESCRIBE（即desc命令）,and you can add to the table a new column with the same name as an unused column.

注释：

SET UNUSED Clause的作用是删除了列在数据字典表DBA_tables中列名等信息，所以the names and types of columns markedUNUSED will not be displayed during a DESCRIBE（即desc命令），但并没有将表里该列上的数据删除而回收空间（故不影响与存储空间相关的数据字典表信息）。而DROP Clause则SpecifyDROP to remove the column descriptor and the data associated with the target column from each row in the table.

关于oracle执行SET UNUSED Clause时的内部操作，可以用10046或是sql_trace来跟踪。

Note:

Until you actually drop these columns, they continue to count toward the absolute limit of 1000 columns in a single table. 【译文：除非你真正drop了这些unused的列，否则在单个表最多有1000列数的这个规定看来，它们依旧算是占用了一个列的位置】
However, as with all DDL statements, you cannot roll back the results of this clause. You cannot issueSETUSED counterpart to retrieve a column that you haveSET UNUSED. Refer to CREATE TABLE for more information on the 1000-column limit.

Also, if you mark a LONG column as UNUSED, then you cannot add another LONG column to the table until you actually drop the unusedLONG column.

DROP Clause

SpecifyDROP to remove the column descriptor and the data associated with the target column from each row in the table.If you explicitly drop a particular column, then all columns currently markedUNUSED in the target table are dropped at the same time.

When the column data is dropped:

• All indexes defined on any of the target columns are also dropped.

• All constraints that reference a target column are removed.

• If any statistics types are associated with the target columns, then Oracle Database disassociates the statistics from the column with theFORCE option and drops any statistics collected using the statistics type.

Note:

If the target column is a parent key of a nontarget column, or if a check constraint references both the target and nontarget columns, then Oracle Database returns an error and does not drop the column unless you have specified the CASCADE CONSTRAINTS clause. If you have specified that clause, then the database removes all constraints that reference any of the target columns.
注释：
target column就是指被DROP Clause所操作的列。
nontarget column就是指不被DROP Clause所操作的列。

See Also:

DISASSOCIATE STATISTICS for more information on disassociating statistics types

DROP UNUSED COLUMNS Clause

Specify DROP UNUSED COLUMNS to remove from the table all columns currently marked as unused. Use this statement when you want to reclaim the extra disk space from unused columns in the table.（即可以回收空间）If the table contains no unused columns, then the statement returns with no errors.

column

 Specify one or more columns to be set as unused or dropped. Use theCOLUMN keyword only if you are specifying only one column. If you specify a column list, then it cannot contain duplicates（复制品）.

CASCADE CONSTRAINTS

Specify CASCADE CONSTRAINTS if youwant to dropall foreign key constraints that refer to the primary and unique keys defined on the dropped columnsas well as all multicolumn constraints defined on the dropped columns.If any constraint is referenced by columns from other tables or remaining columns in the target table【译文：如果存在任何被其他表的列或是目标表的剩余列所引用或说使用的约束，注释：目标表，即DROP UNUSED COLUMNS Clause所要操作的那个表，1、constraint is referenced by columns from other tables就是前一句所说的all foreign key constraints？，2、constraint is referenced by  remaining columns in the target table就是前一句所说的all multicolumn constraints这种情况？】, then you must specifyCASCADE CONSTRAINTS. Otherwise, the statement aborts（中止） and an error is returned.

注释：

altertable emp set unused ( manager_id);后如果约束只是基于set unused里的列（如这里列 manager_id）所创建的，则该约束会自动被删除，不必等到drop column时带上CASCADECONSTRAINTS语句来删除该约束。

INVALIDATE 

The INVALIDATE keyword is optional. Oracle Database automatically invalidates all dependent objects, such as views, triggers, and stored program units. Object invalidation is a recursive process. Therefore, all directly dependent and indirectly dependent objects are invalidated. However, only local dependencies are invalidated, because the database manages remote dependencies differently from local dependencies.

An object invalidated by this statement is automatically revalidated when next referenced. You must then correct any errors that exist in that object before referencing it.

See Also:

Oracle Database Concepts for more information on dependencies

CHECKPOINT

Specify CHECKPOINT if you want Oracle Database to apply a checkpoint for theDROPCOLUMN operation after processing integer rows;integer is optional and must be greater than zero. Ifinteger is greater than the number of rows in the table, then the database applies a checkpoint after all the rows have been processed. If you do not specifyinteger, then the database sets the default of 512. Checkpointing cuts down the amount of undo logs accumulated during theDROP COLUMN operation to avoid running out of undo space. However, if this statement is interrupted after a checkpoint has been applied, then the table remains in an unusable state. While the table is unusable, the only operations allowed on it areDROP TABLE, TRUNCATE TABLE, andALTER TABLE DROP ... COLUMNS CONTINUE (described in sections that follow).

You cannot use this clause withSETUNUSED, because that clause does not remove column data（即列名等列的相关信息）.

参见：

Oracle set unused的用法

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_3001.htm#i2103924

alter table set unused之后各种情况处理