ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 9i下删除unused column的异常情况

9i下删除unused column的异常情况

原创 Linux操作系统 作者:magic007 时间:2008-01-19 16:14:05 0 删除 编辑

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

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69924/viewspace-153376/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-01-13

  • 博文量
    18
  • 访问量
    94335