Drop column时为什么要有undo data?

solearn

最初由 Yong Huang 发布
[B]

If I implied that system rollback segment was only meant for Oracle's internal operation (i.e. recursive SQL), I was wrong. Oracle decides which rollback segment to use based on a set of rules. I remember seeing all the rules in Rama Velpuri's book "Oracle Backup and Recovery" (probably within the first 50 pages). System rollback segment is way down the list. It's NOT exclusively used to record data dictionary changes (i.e. recursive calls).

Yong Huang [/B]

Did you mean that the system rollback segment may be involved in saving data block undo?

In this case, I did not see 'writes' for system rollback segment which contains dictionary (that is, definition for table). And obviously, this operation definitely changes the table and column information. So I was wondering why no signs of change on system rollback segment.

To extend this issue, how DDLs activities can be discovered? search the redo and undo block dump? or any other views?

SONGHEFEI

SQL> create table test_droptable (a char(10),b number);

Table created.

SQL> insert into test_droptable values('1',1);

1 row created.

SQL> insert into test_droptable values('2',2);

1 row created.

SQL> commit ;

Commit complete.

SQL> select dbms_rowid.rowid_block_number(rowid),b from test_droptable;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)          B
------------------------------------ ----------
                                 432          1
                                 432          2
SQL> select HEADER_FILE,HEADER_BLOCK from dba_segments ds where ds.segment_name='TEST_DROPTABLE' ;

HEADER_FILE HEADER_BLOCK
----------- ------------
         11          427


SQL> alter system dump datafile 11 block 432;

System altered.

SQL> alter table test_droptable drop column b;

Table altered.

SQL> alter system dump datafile 11 block 432;

System altered.


我们对比前后的trace file :

前:

** 2006-07-14 22:16:48.000
Start dump data blocks tsn: 12 file#: 11 minblk 432 maxblk 432
buffer tsn: 12 rdba: 0x02c001b0 (11/432)
scn: 0x0000.0124a9c4 seq: 0x01 flg: 0x02 tail: 0xa9c40601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump:  0x02c001b0
Object id on Block? Y
seg/obj: 0x76ef  csc: 0x00.124a9c0  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x2c001a9 ver: 0x01
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.023.00003ea4  0x0080008e.080f.23  --U-    2  fsc 0x0000.0124a9c4
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

data_block_dump,data header at 0xb0f1064
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x0b0f1064
bdba: 0x02c001b0
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f76
avsp=0x1f60
tosp=0x1f60
0xeti[0]        nrow=2        offs=0
0x12ri[0]        offs=0x1f87
0x14ri[1]        offs=0x1f76
block_row_dump:
tab 0, row 0, @0x1f87
tl: 17 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [10]  31 20 20 20 20 20 20 20 20 20
col  1: [ 2]  c1 02
tab 0, row 1, @0x1f76
tl: 17 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [10]  32 20 20 20 20 20 20 20 20 20
col  1: [ 2]  c1 03
end_of_block_dump
End dump data blocks tsn: 12 file#: 11 minblk 432 maxblk 432


后:

*** 2006-07-14 22:17:57.000
Start dump data blocks tsn: 12 file#: 11 minblk 432 maxblk 432
buffer tsn: 12 rdba: 0x02c001b0 (11/432)
scn: 0x0000.0124b0d1 seq: 0x01 flg: 0x02 tail: 0xb0d10601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump:  0x02c001b0
Object id on Block? Y
seg/obj: 0x76ef  csc: 0x00.124b0ce  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x2c001a9 ver: 0x01
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.023.00003ea4  0x0080008e.080f.23  C---    0  scn 0x0000.0124a9c4
0x02   0x0009.002.00003ea6  0x0080008f.080f.3c  --U-    2  fsc 0x0006.0124b0d1

data_block_dump,data header at 0xb0f1064
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x0b0f1064
bdba: 0x02c001b0
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f76
avsp=0x1f60
tosp=0x1f66
0xeti[0]        nrow=2        offs=0
0x12ri[0]        offs=0x1f87
0x14ri[1]        offs=0x1f76
block_row_dump:
tab 0, row 0, @0x1f87
tl: 14 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [10]  31 20 20 20 20 20 20 20 20 20
tab 0, row 1, @0x1f76
tl: 14 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [10]  32 20 20 20 20 20 20 20 20 20
end_of_block_dump
End dump data blocks tsn: 12 file#: 11 minblk 432 maxblk 432

我们是否可以断定:drop 后dump 出的trace file 已经不存在col 1 ,那么也就可以断定块发生了改变,也就是要产生undo record ,用来进行rollback 以及consistent reads！

Yong Huang

最初由 solearn 发布
[B]

Did you mean that the system rollback segment may be involved in saving data block undo? ...
[/B]

No. I meant the opposite! I said that the system rollback segment is not special in the sense that it's used to store data dictionary undo. Not at all. Instead, it's special in the sense that its usage is very low in priority. That is, if no non-system rollback segments are available to use, then it will be considered. The exact consideration order is in Rama Velpuri's book and may also be in some Metalink document.

Also see my old message where I recalled what I read from Rama's book:
http://www.lazydba.com/oracledba.pl?0::6301:g

Yong Huang

zouzhiyou

学习了

solearn

最初由 SONGHEFEI 发布
[B]

SQL> alter system dump datafile 11 block 432;

System altered.

SQL> alter table test_droptable drop column b;

Table altered.

SQL> alter system dump datafile 11 block 432;

System altered.



我们是否可以断定:drop 后dump 出的trace file 已经不存在col 1 ,那么也就可以断定块发生了改变,也就是要产生undo record ,用来进行rollback 以及consistent reads！ [/B]

谢谢答复！

从你的实验中可以看到，oracle改写了行的内容，生成了相应的undo（有点类似于update）。同时drop column是个DDL语句，应该更新了数据字典。

solearn

最初由 Yong Huang 发布
[B]

No. I meant the opposite! I said that the system rollback segment is not special in the sense that it's used to store data dictionary undo. Not at all. Instead, it's special in the sense that its usage is very low in priority. That is, if no non-system rollback segments are available to use, then it will be considered. The exact consideration order is in Rama Velpuri's book and may also be in some Metalink document.

Also see my old message where I recalled what I read from Rama's book:
http://www.lazydba.com/oracledba.pl?0::6301:g

Yong Huang [/B]

Could you offer any other links? I can't get the right docs from http://www.lazydba.com/oracledba.pl?0::6301:g and have no metalink access. Thanks.

Yong Huang

最初由 solearn 发布
[B]

Could you offer any other links? I can't get the right docs from http://www.lazydba.com/oracledba.pl?0::6301:g and have no metalink access. Thanks. [/B]

Sorry for that link. I think it worked the other day. Try this:
http://www.lazydba.com/oracle/0__6281.html

I just pulled the book off my shelf. If you have it, it's on p.57.

Yong Huang