结论
1,alter table set unused column指定表的某列为不可用
2,alter table set unused column生效后不可用列已经不再属于表，但空间不会释放，需要重组方可释放
3,alter table set unused column的信息可用user_unused_col_tabs查询
4,alter table set unused column适用于高并发OLTP环境，在业务繁忙时，先运行此语句，待业务压力小时，然后用alter table drop unused columns真正删除不可用的列;
alter table drop column删除列消耗的时间明显要高于alter table set unused column
5,alter table set unused column指定某个LONG列不可用，不能添加其它LONG列
6，同一个表中不能同时存在2个LONG，其它LOB列不受其限制
7，通过alter table set unsed(表中列的列表，以逗号分割)，可以同时指定多个列不可用
8, alter table set unused column cascade constraints配置某列不可用会递归删除定义的约束,发现指不指定cascade constraints选项都会递归删除其约束
9, 通过alter table set unsed(表中列的列表，以逗号分割)，可以同时指定多个列不可用
测试
1，数据库版本
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
2,获取暂时不使用的列的表信息
SQL> select count(*) from user_unused_col_tabs;
COUNT(*)
----------
0
3，创建某个测试表
SQL> create table t_unused_col(a int,b int,c int);
Table created.
4,配置指定列为不可用
查阅官方手册，配置表的列为不可用，其语法请参考drop_column_clause 节
SQL> alter table t_unused_col set unused column b;
Table altered.
SQL> alter table t_unused_col set unused column c;
Table altered.
SQL> select count(*) from user_unused_col_tabs;
COUNT(*)
----------
1
5，配置指定不可用后不再显示其列
SQL> desc user_unused_col_tabs;
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
COUNT NUMBER
6，可以在如下字典查询到不可用列的相关信息
SQL> select table_name,count from user_unused_col_tabs;
TABLE_NAME COUNT
------------------------------------------------------------ ----------
T_UNUSED_COL 2
SQL> insert into t_unused_col values(1,1,1);
insert into t_unused_col values(1,1,1)
*
ERROR at line 1:
ORA-00913: too many values
SQL> insert into t_unused_col values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t_unused_col;
A
----------
1
7,删除测试表中不可用的列
SQL> alter table t_unused_col drop unused columns;
Table altered.
8，重新添加B与C列
SQL> alter table t_unused_col add b int;
Table altered.
SQL> alter table t_unused_col add c int;
Table altered.
9，配置某列为不可用后，可以添加同名的列
SQL> alter table t_unused_col set unused column b;
Table altered.
SQL> alter table t_unused_col add b int;
Table altered.
10，配置某列为不可用，其列占用的空间不会释放
SQL> drop table t_unused_col purge;
Table dropped.
SQL> create table t_unused_col(a int,b int,c int);
Table created.
SQL> insert into t_unused_col select level,level,level from dual connect by level<=100000;
100000 rows created.
SQL> commit;
Commit complete.
SQL> select segment_name,bytes/1024/1024 from user_segments where segment_name='T_UNUSED_COL';
SEGMENT_NAME BYTES/1024/1024
------------------------------ ---------------
T_UNUSED_COL 3
SQL> alter table t_unused_col set unused column b;
Table altered.
SQL> select segment_name,bytes/1024/1024 from user_segments where segment_name='T_UNUSED_COL';
SEGMENT_NAME BYTES/1024/1024
------------------------------ ---------------
T_UNUSED_COL 3
11，删除表中不可用的列后，其空间仍不会释放
SQL> alter table t_unused_col drop unused columns;
Table altered.
SQL> select segment_name,bytes/1024/1024 from user_segments where segment_name='T_UNUSED_COL';
SEGMENT_NAME BYTES/1024/1024
------------------------------ ---------------
T_UNUSED_COL 3
12,只有重组表后，才会释放删除不可用列的空间
SQL> alter table t_unused_col move;
Table altered.
SQL> select segment_name,bytes/1024/1024 from user_segments where segment_name='T_UNUSED_COL';
SEGMENT_NAME BYTES/1024/1024
------------------------------ ---------------
T_UNUSED_COL 2
13，配置某个LONG类型的列为不可用后，必须删除其列后方可添加新的LONG列
SQL> alter table t_unused_col add b clob;
Table altered.
SQL> alter table t_unused_col set unused column b;
Table altered.
SQL> alter table t_unused_col add x long;
Table altered.
SQL> alter table t_unused_col set unused column x;
Table altered.
SQL> alter table t_unused_col add y long;
alter table t_unused_col add y long
*
ERROR at line 1:
ORA-01754: a table may
contain only one column of
type LONG
SQL> alter table t_unused_col drop unused columns;
Table altered.
SQL> alter table t_unused_col add y long;
Table altered.
SQL> drop table t_unused_col purge;
Table dropped.
SQL> create table t_unused_col(a int,b long,c long);
create table t_unused_col(a int,b long,c long)
*
ERROR at line 1:
ORA-01754: a table may
contain only one column of
type LONG
14,可见alter table drop columns消耗的时间明显要大于alter table set unused columns，所以后者适用于高并发的OLTP环境，减少锁持有情况发生；
到业务压力不大时，可以采用alter table drop unused columns
SQL> create table t_unused_col(a int,b int);
Table created.
SQL> insert into t_unused_col select level,level from dual connect by level<=1000000;
1000000 rows created.
SQL> commit;
Commit complete.
SQL> set timing on time on
06:34:03 SQL> alter table t_unused_col drop column b;
Table altered.
Elapsed: 00:03:06.30
06:37:42 SQL> drop table t_unused_col purge;
Table dropped.
Elapsed: 00:00:03.94
06:37:56 SQL> create table t_unused_col(a int,b int);
Table created.
Elapsed: 00:00:01.98
06:38:12 SQL> insert into t_unused_col select level,level from dual connect by level<=1000000;
1000000 rows created.
Elapsed: 00:00:15.59
06:40:58 SQL> 06:40:58 SQL> commit;
06:41:11 SQL> alter table t_unused_col set unused column b;
Table altered.
Elapsed: 00:00:00.77
06:41:32 SQL> alter table t_unused_col drop unused columns;
Table altered.
Elapsed: 00:02:22.51
06:44:13 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
15,通过alter table set unsed(表中列的列表，以逗号分割)，可以同时指定多个列不可用
SQL> drop table t_unused_col purge;
Table dropped.
SQL> create table t_unused_col(a int,b int,c int);
Table created.
SQL> alter table t_unused_col set unused (b,c);
Table altered.
16,alter table set unused column cascade constraints配置某列不可用会递归删除定义的约束,发现指不指定cascade constraints选项都会递归删除其约束
SQL> drop table t_unused_col purge;
Table dropped.
SQL> create table t_unused_col(a int,b int);
Table created.
SQL> alter table t_unused_col add constraint chk_b check(b>1);
Table altered.
SQL> select constraint_name,constraint_type,status from user_constraints where table_name='T_UNUSED_COL';
CONSTRAINT_NAME CO STATUS
------------------------------ -- ----------------
CHK_B C ENABLED
SQL> alter table t_unused_col set unused column b cascade constraints;
Table altered.
SQL> select constraint_name,constraint_type,status from user_constraints where table_name='T_UNUSED_COL';
no rows selected
SQL> drop table T_UNUSED_COL purge;
Table dropped.
SQL> drop table t_unused_col purge;
Table dropped.
SQL> create table t_unused_col(a int primary key,b int);
Table created.
SQL> select constraint_name,constraint_type,status from user_constraints where table_name='T_UNUSED_COL';
CONSTRAINT_NAME CO STATUS
------------------------------ -- ----------------
SYS_C0011644 P ENABLED
SQL> alter table t_unused_col set unused column a;
Table altered.
SQL> select constraint_name,constraint_type,status from user_constraints where table_name='T_UNUSED_COL';
no rows selected
出自：