大表drop column很慢如何解决：使用SET UNUSED Clause

第一种方法：

重新建表,把需要的数据导入新建表 用APPEND NOLOG 的HINT，然后rename到原来的表

第二种方法：

在高峰的时候做set unused，在该列做一个删除标志，较空闲时进行楼上的操作drop unused column，真正的删除该列。

先将列unused,然后drop
alter table tab_name set unused column col_name;---速度很快
alter table tab_name drop unused columns;

详细描述如下：

SET UNUSED Clause
Specify SET UNUSED to mark one or more columns as unused. Specifying this clause does not actually remove the target columns from each row in the table (that is, it does not restore the disk space used by these columns). Therefore, the response time is faster than it would be if you execute the DROP clause.

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:
Oracle9i Database Reference for information on the data dictionary views


Unused columns are treated as if they were dropped, even though their column data remains in the table's rows. After a column has been marked UNUSED, you have no access to that column. A "SELECT *" query will not retrieve data from unused columns. In addition, the names and types of columns marked UNUSED will not be displayed during a DESCRIBE, and you can add to the table a new column with the same name as an unused column.


--------------------------------------------------------------------------------
Note:
Until you actually drop these columns, they continue to count toward the absolute limit of 1000 columns in a single table. However, as with all DDL statements, you cannot roll back the results of this clause. That is, you cannot issue SET USED counterpart to retrieve a column that you have SET UNUSED.

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


DROP Clause
Specify DROP 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 marked UNUSED 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 disassociates the statistics from the column with the FORCE 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 returns an error and does not drop the column unless you have specified the CASCADE CONSTRAINTS clause. If you have specified that clause, then Oracle removes all constraints that reference any of the target columns.