下面以例子說(shuō)話：

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

Table created.

看看數(shù)據(jù)字典：

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

通過(guò)DUMP數(shù)據(jù)庫(kù)文件塊可以看到每行有三列(這里不再列出DUMP文件內(nèi)容)

下面將B列置為unused狀態(tài)：

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

這里原來(lái)的B列，其名字為系統(tǒng)自動(dòng)生成的一列，命名形式為SYS_CNNNNN_YYMMDDHH24:MI:SS$，NNNNN為原來(lái)的COLUMN_ID，前面補(bǔ)0補(bǔ)足成5數(shù)。hidden已經(jīng)變?yōu)閅ES，COLUMN_ID為空。其他兩列A和C的COLUMN_ID順序作了調(diào)整。這三列的SEGMENT_COLUMN_ID和INTERNAL_COLUMN_ID沒(méi)有變化。

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列已經(jīng)沒(méi)有顯示出來(lái)。

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#已經(jīng)變成0.SEGCOL#和INTCOL#列沒(méi)有變化，NAME也已經(jīng)變化

DUMP出來(lái)的數(shù)據(jù)中，每一行仍然有三列。

嘗試插入數(shù)據(jù)：

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對(duì)應(yīng)的rfile#為5，block#為302，row number為0

DUMP出這一塊查看第0行數(shù)據(jù)，發(fā)現(xiàn)在數(shù)據(jù)塊中每行仍然是3列，第二列也就是原來(lái)的B列其值為NULL。

現(xiàn)在我們將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列都已經(jīng)刪除。

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列已經(jīng)被刪除。從這個(gè)實(shí)驗(yàn)就可以看出，在刪除C時(shí)會(huì)將UNUSED列一并刪除。

DUMP出數(shù)據(jù)塊可以發(fā)展，塊中每一行只有1列。因此SET UNUSED只是修改了數(shù)據(jù)字典，速度較多。而將COLUMN DROP掉，不僅修改數(shù)據(jù)字典，而且修改實(shí)際的塊數(shù)據(jù)。如果表比較大，會(huì)耗費(fèi)比較長(zhǎng)的時(shí)間。

總結(jié)

以上是生活随笔為你收集整理的oracle查看列状态unused,Oracle9i下将列设为UNUSED时的系统行为的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。