老熊的三分地-Oracle及数据恢复

作了一个实验，删除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