上一篇文章提到压缩表发生update后会导致行迁移，但是在上篇文章里面没有做过多描述
，这次我们来仔细看一下update compressed table会发生什么事情。此外我们再来看看压
缩表结构修改是怎么处理的。

首先创建测试表

create table test2(a varchar2(10),b varchar2(10),c varchar2(10));

begin
for i in 1000000000..1000100000 loop
insert into test2 values(i,'1',to_char(mod(i,100)));
commit;
end loop;
end;
/
SQL 10G>create table testcom4 compress as select * from test2 order by c;
Table created.

对压缩表添加一个列

SQL 10G>SQL 10G>SQL 10G>

alter table testcom4 add d number;

Table altered.

定位到一条记录,找出所在文件号，块号，文件号和rowid

SQL 10G>select dbms_rowid.ROWID_RELATIVE_FNO(rowid) file#,
dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#,
dbms_rowid.ROWID_ROW_NUMBER(rowid) row# from
testcom4 where rownum<2;

FILE# BLOCK# ROW#
---------- ---------- ----------
12 61364 0

SQL 10G>select rowid from testcom4 where rownum<2;

ROWID
------------------
AAAT9AAAMAAAO+0AAA

更新这条记录

SQL 10G>update testcom4 set d=1 where rowid='AAAT9AAAMAAAO+0AAA';

1 row updated.

SQL 10G>commit;

Commit complete.

SQL 10G>select dbms_rowid.ROWID_RELATIVE_FNO(rowid) file#,
dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#,
dbms_rowid.ROWID_ROW_NUMBER(rowid) row# from testcom4
where rowid='AAAT9AAAMAAAO+0AAA';

FILE# BLOCK# ROW#
---------- ---------- ----------
12 61364 0
dump这个block看看行迁移是怎么发生的

SQL 10G>alter system dump datafile 12 block 61364;

System altered.

perm_9ir2[3]={ 2 0 1 }
...
block_row_dump:
tab 0, row 0, @0x1f79
tl: 7 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 1] 31
col 1: [ 1] 30
bindmp: 01 bc 02 c9 31 c9 30
tab 1, row 0, @0x1f69
tl: 9 fb: --H----- lb: 0x2 cc: 0
nrid: 0x0300f085.0 这里指向了新的数据块
bindmp: 20 02 00 03 00 f0 85 00 00
定位新的块

SQL 10G>select dbms_utility.DATA_BLOCK_ADDRESS_FILE(to_number('300f085','xxxxxxxxxx'))
file#,dbms_utility.DATA_BLOCK_ADDRESS_BLOCK(to_number('300f085','xxxxxxxxxx'))
block# from dual;

FILE# BLOCK#
---------- ----------
12 61573

dump新的block

SQL 10G>alter system dump datafile 12 block 61573;

System altered.

block_row_dump:
tab 0, row 0, @0x1f65
tl: 27 fb: ----FL-- lb: 0x1 cc: 4
hrid: 0x0300efb4.0
col 0: [10] 31 30 30 30 30 39 33 30 30 30
col 1: [ 1] 31
col 2: [ 1] 30
col 3: [ 2] c1 02

可以看到新的block里面已经是非压缩的数据格式了，从这里可以看出对压缩表的更新确实是会导致
压缩失效。
那么能不能删除新加的列呢？试一下

SQL 10G>alter table testcom4 drop column d;
alter table testcom4 drop column d
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

报错了，提示“unsupported add/drop column operation on compressed tables”
metalink上说这是oracle的一个bug，在10g修复，但是在我的10g r2的版本上还是
不通过。9i的版本更加离谱，连add column都不行。

－－－－－－－－－－－－－－－－－－－－－－－－－－－
SQL 9I> alter table testcom4 add d number;
alter table testcom4 add d number
*
ERROR at line 1:
ORA-22856: cannot add columns to object tables
－－－－－－－－－－－－－－－－－－－－－－－－－－－
10g可以进行set unused的操作

SQL 10G>alter table testcom4 set unused column d;

Table altered.
但是drop unused columns依然报错，依然是一个bug

SQL 10G>alter table testcom4 drop unused columns;
alter table testcom4 drop unused columns
*
ERROR at line 1:
ORA-12996: cannot drop system-generated virtual column

希望下次下载一个patch可以解决这些问题。