作了一个实验，删除unused column时出现异常情况：
SQL> create table test1.t1 (a number,b number, c number);
Table created.
SQL> alter table test1.t1 set unused (a);
Table altered.
SQL> alter table test1.t1 set unused (b);
Table altered.
SQL> select object_id from dba_objects where object_name='T1';
OBJECT_ID
----------
6578
SQL> select col#,segcol#,name,intcol# from col$ where obj#=6578;
COL# SEGCOL# NAME INTCOL#
---------- ---------- ---------------------------- ----------
0 1 SYS_C00001_08011916:08:41$ 1
0 2 SYS_C00002_08011916:08:47$ 2
1 3 C 3
SQL> alter table test1.t1 drop (b);
alter table test1.t1 drop (b)
*
ERROR at line 1:
ORA-00904: "B": invalid identifier
SQL> alter table test1.t1 drop (a);
alter table test1.t1 drop (a)
*
ERROR at line 1:
ORA-00904: "A": invalid identifier
SQL> alter table test1.t1 drop (SYS_C00002_08011916:08:47$);
alter table test1.t1 drop (SYS_C00002_08011916:08:47$)
*
ERROR at line 1:
ORA-00907: missing right parenthesis
SQL> alter table test1.t1 drop ("SYS_C00002_08011916:08:47$");
Table altered.
SQL> select col#,segcol#,name,intcol# from col$ where obj#=6578;
COL# SEGCOL# NAME INTCOL#
---------- ---------- ---------------------------- ----------
1 0 C 0
这里segcol#和intcol#居然为0.
SQL> select * from test1.t1;
no rows selected
SQL> insert into test1.t1 values (1);
1 row created.
SQL> select * from test1.t1;
SQL> select * from test1.t1;
C
----------
SQL> commit;
Commit complete.
SQL> select * from test1.t1;
C
----------
SQL> select * from test1.t1;
C
----------
SQL> insert into test1.t1 values (2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test1.t1;
C
----------
SQL> select count(*) from test1.t1;
COUNT(*)
----------
2
虽然可以看到有两行数据，但是查询出的值均为NULL。
将数据块DUMP数据，看到的列数居然是0.：
tab 0, row 0, @0xf95
tl: 3 fb: --H-FL-- lb: 0x1 cc: 0
tab 0, row 1, @0xf92
tl: 3 fb: --H-FL-- lb: 0x2 cc: 0
可见此处出现了异常。
而按正常做法则没有什么问题：
SQL> drop table t1;
Table dropped.
SQL> create table test1.t1 (a number,b number,c number, d number);
Table created.
SQL> alter table test1.t1 set unused (a);
Table altered.
SQL> alter table test1.t1 set unused (b);
Table altered.
SQL> alter table test1.t1 drop (d);
Table altered.
SQL> insert into test1.t1 values (1);
1 row created.
SQL> insert into test1.t1 values (2);
1 row created.
SQL> select * from test1.t1;
C
----------
1
2
看起来还是不要删除本身就已经是UNUSED的列。
下面再看看这种做法对已经有数据的表的影响：
SQL> drop table test1.t1;
Table dropped.
SQL> create table test1.t1 as select rownum a,rownum*10 b,rownum*100 c from dba_
objects where rownum<=100;
Table created.
SQL> alter table test1.t1 set unused (b);
Table altered.
SQL> alter table test1.t1 set unused (c);
Table altered.
SQL> select object_id from dba_objects where object_name='T1';
OBJECT_ID
----------
6581
SQL> select col#,name,segcol#,intcol# from col$ where obj#=6581;
COL# NAME SEGCOL# INTCOL#
---------- ---------------------------- ---------- ----------
1 A 1 1
0 SYS_C00002_08011916:34:21$ 2 2
0 SYS_C00003_08011916:34:23$ 3 3
SQL> alter table test1.t1 drop ("SYS_C00003_08011916:34:23$");
Table altered.
SQL> select col#,name,segcol#,intcol# from col$ where obj#=6581;
COL# NAME SEGCOL# INTCOL#
---------- ---------------------------- ---------- ----------
1 A 1 1
SQL> select * from test1.t1 where rownum<=10;
A
----------
现在数据仍然是空，出现了异常。因此在9i下强制删除unused column是有问题的。我认为这里一个BUG。不知道10g是不是还是这种情况。
删除unused column的正确方法仍然是：
ALTER TABLE ...... DROP UNUSED COLUMNS
