oracle查看列状态unused,Oracle9i下将列设为UNUSED时的系统行为

下面以例子说话：

SQL> create table test1.unused_test  as select rownum a,rownum*2 b,rownum*10 c from dba_objects where rownum<=100;

Table created.

看看数据字典：

SQL> select column_name,data_type,column_id,hidden_column,segment_column_id seg_

cid,internal_column_id internal_cid from dba_tab_cols where wner='TEST1' and ta

ble_name='UNUSED_TEST';

COLUMN_NAM DATA_TYPE            COLUMN_ID HIDDEN    SEG_CID INTERNAL_CID

---------- -------------------- --------- ------ ---------- ------------

A          NUMBER                       1 NO              1            1

B          NUMBER                       2 NO              2            2

C          NUMBER                       3 NO              3            3

SQL> select column_name,data_type,column_id from dba_tab_columns where wner='TE

ST1' and table_name='UNUSED_TEST';

COLUMN_NAM DATA_TYPE            COLUMN_ID

---------- -------------------- ---------

A          NUMBER                       1

B          NUMBER                       2

C          NUMBER                       3

SQL> select object_id from dba_objects where wner='TEST1' and object_name='UNUS

ED_TEST' and object_type='TABLE';

OBJECT_ID

----------

6577

SQL> select col#,segcol#,name,intcol# from col$ where obj#=6577;

COL#    SEGCOL# NAME          INTCOL#

---------- ---------- ---------- ----------

1          1 A                   1

2          2 B                   2

3          3 C                   3

通过DUMP数据库文件块可以看到每行有三列(这里不再列出DUMP文件内容)

下面将B列置为unused状态：

SQL> alter table test1.unused_test set unused (b);

Table altered.

SQL> select column_name,data_type,column_id,hidden_column,segment_column_id seg_

cid,internal_column_id internal_cid from dba_tab_cols where wner='TEST1' and ta

ble_name='UNUSED_TEST';

COLUMN_NAME                  DATA_T COLUMN_ID HIDDEN    SEG_CID INTERNAL_CID

---------------------------- ------ --------- ------ ---------- ------------

A                            NUMBER         1 NO              1            1

SYS_C00002_08011915:24:34$   NUMBER           YES             2            2

C                            NUMBER         2 NO              3            3

这里原来的B列，其名字为系统自动生成的一列，命名形式为SYS_CNNNNN_YYMMDDHH24:MI:SS$，NNNNN为原来的COLUMN_ID，前面补0补足成5数。hidden已经变为YES，COLUMN_ID为空。其他两列A和C的COLUMN_ID顺序作了调整。这三列的SEGMENT_COLUMN_ID和INTERNAL_COLUMN_ID没有变化。

SQL> select column_name,data_type,column_id from dba_tab_columns where wner='TE

ST1' and table_name='UNUSED_TEST';

COLUMN_NAME                  DATA_T COLUMN_ID

---------------------------- ------ ---------

A                            NUMBER         1

C                            NUMBER         2

在DBA_TAB_COLUMNS视图中，B列已经没有显示出来。

SQL> select col#,segcol#,name,intcol# from col$ where obj#=6577;

COL#    SEGCOL# NAME                            INTCOL#

---------- ---------- ---------------------------- ----------

1          1 A                                     1

0          2 SYS_C00002_08011915:24:34$            2

2          3 C                                     3

这里B列的COL#已经变成0.SEGCOL#和INTCOL#列没有变化，NAME也已经变化

DUMP出来的数据中，每一行仍然有三列。

尝试插入数据：

SQL> insert into test1.unused_test values (1234,4321,1);

insert into test1.unused_test values (1234,4321,1)

*

ERROR at line 1:

ORA-00913: too many values

SQL> insert into test1.unused_test values (1234,4321);

1 row created.

SQL> select rowid from test1.unused_test where a=1234 and c=4321;

ROWID

------------------

AAABmxAAFAAAAEuAAA

此ROWID对应的rfile#为5，block#为302，row number为0

DUMP出这一块查看第0行数据，发现在数据块中每行仍然是3列，第二列也就是原来的B列其值为NULL。

现在我们将C列删除：

SQL> alter table test1.unused_test drop (c);

Table altered.

SQL> select column_name,data_type,column_id,hidden_column,segment_column_id seg_

cid,internal_column_id internal_cid from dba_tab_cols where wner='TEST1' and ta

ble_name='UNUSED_TEST';

COLUMN_NAME                  DATA_T COLUMN_ID HIDDEN    SEG_CID INTERNAL_CID

---------------------------- ------ --------- ------ ---------- ------------

A                            NUMBER         1 NO              1            1

这里可以看出B列和C列都已经删除。

SQL> select column_name,data_type,column_id from dba_tab_columns where wner='TE

ST1' and table_name='UNUSED_TEST';

COLUMN_NAME                  DATA_T COLUMN_ID

---------------------------- ------ ---------

A                            NUMBER         1

SQL> select col#,segcol#,name,intcol# from col$ where obj#=6577;

COL#    SEGCOL# NAME                            INTCOL#

---------- ---------- ---------------------------- ----------

1          1 A                                     1

都可以看出B列和C列已经被删除。从这个实验就可以看出，在删除C时会将UNUSED列一并删除。

DUMP出数据块可以发展，块中每一行只有1列。因此SET UNUSED只是修改了数据字典，速度较多。而将COLUMN DROP掉，不仅修改数据字典，而且修改实际的块数据。如果表比较大，会耗费比较长的时间。