发生在UPDATE，原块中容纳不下了，那么就得把修改内容放在别的块中，原块中只放了行头和指针，数据跑到别的块中去了
SQL> @?/rdbms/admin/utlchain.sql
SQL> create tablespace test_tbs DATAFILE '/u01/app/oracle/oradata/shujukuai/test_tbs.dbf' size 1m reuse;
Tablespace created.
SQL> select tablespace_name||'|'||SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where tablespace_name='TEST_TBS';
TABLESPACE_NAME||'|'||SEGMENT_SPACE_M
-------------------------------------
TEST_TBS|AUTO
SQL> create table CHAINED_TEST (id int,vc varchar2(4000)) tablespace test_tbs;
Table created.
SQL> insert into CHAINED_TEST values (1,'a');
1 row created.
SQL> insert into CHAINED_TEST values (2,'b');
1 row created.
SQL> insert into CHAINED_TEST values (3,'c');
1 row created.
SQL> insert into CHAINED_TEST values (4,'d');
1 row created.
SQL> commit;
Commit complete.
SQL> set serveroutput on
SQL> exec show_space('CHAINED_TEST',user,'TABLE');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ...................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 5
Full Blocks ............................ 0
Total Blocks............................ 8
Total Bytes............................. 65,536
Total MBytes............................ 0
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 11
Last Used Ext BlockId................... 9
Last Used Block......................... 8
PL/SQL procedure successfully completed.
SQL> set autotrace on stat
SQL> update CHAINED_TEST set vc=rpad('1',3999,'*') where id = 1;
1 row updated.
Statistics
----------------------------------------------------------
48 recursive calls
4 db block gets
28 consistent gets
0 physical reads
4512 redo size
2083 bytes sent via SQL*Net to client
1506 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> update CHAINED_TEST set vc=rpad('2',3999,'*') where id = 2;
1 row updated.
Statistics
----------------------------------------------------------
5 recursive calls
2 db block gets
18 consistent gets
0 physical reads
4364 redo size
1139 bytes sent via SQL*Net to client
1211 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> commit;
Commit complete.
SQL> exec show_space('CHAINED_TEST',user,'TABLE');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ...................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 4
Full Blocks ............................ 1
Total Blocks............................ 8
Total Bytes............................. 65,536
Total MBytes............................ 0
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 11
Last Used Ext BlockId................... 9
Last Used Block......................... 8
PL/SQL procedure successfully completed.
分析表
SQL> analyze table CHAINED_TEST list chained rows ;
Table analyzed.
SQL> set autot off
SQL> select * from CHAINED_ROWS;
no rows selected 没有发生行迁移的行
SQL> set autotrace on stat
SQL> update CHAINED_TEST set vc=rpad('3',3999,'*') where id = 3;
1 row updated.
Statistics
----------------------------------------------------------
5 recursive calls
9 db block gets
27 consistent gets
0 physical reads
5092 redo size
2082 bytes sent via SQL*Net to client
1506 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> analyze table CHAINED_TEST list chained rows ;
Table analyzed.
SQL> set autot off
SQL> set linesize 180
SQL> col owner_name for a10
SQL> col table_name for a10
SQL> col cluster_name for a10
SQL> col partition_name for a10
SQL> col subpartition_name for a10
SQL> select * from CHAINED_ROWS;
OWNER_NAME TABLE_NAME CLUSTER_NA PARTITION_ SUBPARTITI HEAD_ROWID ANALYZE_T
---------- ---------- ---------- ---------- ---------- ------------------ ---------
SYS CHAINED_TEST N/A AAAMpoAALAAAAAMAAC 05-JUN-10
SQL> select distinct dbms_rowid.rowid_block_number(rowid) from CHAINED_TEST;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
12
SQL> select file_id from dba_data_files where tablespace_name='TEST_TBS';
FILE_ID
----------
11
SQL> oradebug setmypid
Statement processed.
SQL> alter system dump datafile 11 block 12;
System altered.
SQL> oradebug tracefile_name
/u01/app/oracle/admin/shujukuai/udump/shujukuai_ora_13072.trc
SQL> host vi /u01/app/oracle/admin/shujukuai/udump/shujukuai_ora_13072.trc
/u01/app/oracle/admin/shujukuai/udump/shujukuai_ora_13072.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: RHEL5.4
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine: x86_64
Instance name: shujukuai
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 13072, image: oracle@RHEL5.4 (TNS V1-V3)
*** 2010-06-05 23:54:32.067
*** ACTION NAME:() 2010-06-05 23:54:32.066
*** MODULE NAME:(sqlplus@RHEL5.4 (TNS V1-V3)) 2010-06-05 23:54:32.066
*** SERVICE NAME:(SYS$USERS) 2010-06-05 23:54:32.066
*** SESSION ID:(131.2480) 2010-06-05 23:54:32.066
Start dump data blocks tsn: 12 file#: 11 minblk 12 maxblk 12
buffer tsn: 12 rdba: 0x02c0000c (11/12)
scn: 0x0000.000c9082 seq: 0x01 flg: 0x06 tail: 0x90820601
frmt: 0x02 chkval: 0x5d67 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000000115FB600 to 0x00000000115FD600
0115FB600 0000A206 02C0000C 000C9082 06010000 [................]
0115FB610 00005D67 00000001 0000CA68 000C904A [g]......h...J...]
0115FB620 00000000 00320002 02C00009 00050005 [......2.........]
0115FB630 00000157 008030EC 000E00F9 00002001 [W....0....... ..]
0115FB640 000C9082 00200008 0000015E 00803276 [...... .^...v2..]
0115FB650 0019012E 00008000 000C8FE9 00000000 [................]
0115FB660 00000000 00040100 001AFFFF 001C001F [................]
0115FB670 0000001C 0FD00004 001F0028 00001F78 [........(...x...]
0115FB680 20000000 C0020001 00001000 0202002C [... ........,...]
0115FB690 9FFE03C1 2A2A320F 2A2A2A2A 2A2A2A2A [.....2**********]
0115FB6A0 2A2A2A2A 2A2A2A2A 2A2A2A2A 2A2A2A2A [****************]
Repeat 248 times
0115FC630 2A2A2A2A 0202002C 9FFE02C1 2A2A310F [****,........1**]
0115FC640 2A2A2A2A 2A2A2A2A 2A2A2A2A 2A2A2A2A [****************]
Repeat 248 times
0115FD5D0 2A2A2A2A 2A2A2A2A 2A2A2A2A 0202002C [************,...]
0115FD5E0 640105C1 0202012C 630104C1 0202002C [...d,......c,...]
0115FD5F0 620103C1 0202002C 610102C1 90820601 [...b,......a....]
Block header dump: 0x02c0000c
Object id on Block? Y
seg/obj: 0xca68 csc: 0x00.c904a itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2c00009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.005.00000157 0x008030ec.00f9.0e --U- 1 fsc 0x0000.000c9082
0x02 0x0008.020.0000015e 0x00803276.012e.19 C--- 0 scn 0x0000.000c8fe9
data_block_dump,data header at 0x115fb664
===============
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x115fb664
bdba: 0x02c0000c
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f
avsp=0x1c
tosp=0x1c
0xe:pti[0] nrow=4 ffs=0
0x12:pri[0] ffs=0xfd0
0x14:pri[1] ffs=0x28
0x16:pri[2] ffs=0x1f
0x18:pri[3] ffs=0x1f78
block_row_dump:
tab 0, row 0, @0xfd0
tl: 4008 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [3999]
31 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a
2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a
2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a
2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a
2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a
。。。。。
tab 0, row 2, @0x1f
tl: 9 fb: --H----- lb: 0x1 cc: 0 这里只有一个H头了
nrid: 0x02c00010.0 指向了迁移的地址
tab 0, row 3, @0x1f78
tl: 8 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 05
col 1: [ 1] 64
end_of_block_dump
End dump data blocks tsn: 12 file#: 11 minblk 12 maxblk 12
SQL> set autot on
SQL> exec show_space('CHAINED_TEST',user,'TABLE');
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> exec show_space('CHAINED_TEST',user,'TABLE');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ...................... 0
FS2 Blocks (25-50) ..................... 1
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 3
Full Blocks ............................ 1
Total Blocks............................ 8
Total Bytes............................. 65,536
Total MBytes............................ 0
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 11
Last Used Ext BlockId................... 9
Last Used Block......................... 8
PL/SQL procedure successfully completed.
SQL> alter table CHAINED_TEST move ;
Table altered.
SQL> exec show_space('CHAINED_TEST',user,'TABLE');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ...................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ............................ 3
Total Blocks............................ 8
Total Bytes............................. 65,536
Total MBytes............................ 0
Unused Blocks........................... 2
Unused Bytes............................ 16,384
Last Used Ext FileId.................... 11
Last Used Ext BlockId................... 17
Last Used Block......................... 6
PL/SQL procedure successfully completed.
SQL> select rowid from Chained_Test where id = 3; 都降低了高水位，行迁移还存在的
ROWID
------------------
AAAMppAALAAAAAWAAA
创建中间表，保留迁移的数据
SQL> CREATE TABLE Int_Chained_Test
AS SELECT *
FROM Chained_Test
WHERE ROWID IN
(SELECT HEAD_ROWID
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'CHAINED_TEST');
Table created.
删除迁移行
SQL>DELETE FROM Chained_Test
WHERE ROWID IN
(SELECT HEAD_ROWID
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'CHAINED_TEST');
0 rows deleted.
将迁移出的行插入
SQL>INSERT INTO CHAINED_TEST SELECT * FROM Int_Chained_Test;
no rows selected
为什么呢，没行数据
SQL> select rowid from Chained_Test where id = 3;
ROWID
------------------
AAAMppAALAAAAAWAAA
SQL> select * from CHAINED_ROWS;
OWNER_NAME TABLE_NAME CLUSTER_NA PARTITION_ SUBPARTITI HEAD_ROWID ANALYZE_T
---------- --------------- ---------- ---------- ---------- ------------------ ---------
SYS CHAINED_TEST N/A AAAMpoAALAAAAAMAAC 05-JUN-10
晕，这两个ROWID没对应上。怀疑是MOVE表的时候变更了
SQL> commit;
Commit complete.
SQL> truncate table Chained_Rows;
Table truncated.
SQL> analyze table Chained_Test list chained rows ;
Table analyzed.
SQL> select * from Chained_Rows;
no rows selected
SQL> select rowid from Chained_Test where id = 3;
ROWID
------------------
AAAMppAALAAAAAWAAA
SQL> set autot traceonly statistics
SQL> select * from Chained_Test where rowid ='AAAMppAALAAAAAWAAA';
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets 只有一次一致性读了。说明没有发生行迁移了撒
0 physical reads
0 redo size
4586 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> DROP TABLE Int_Chained_Test;
Table dropped.
SQL> DELETE FROM CHAINED_ROWS WHERE TABLE_NAME = 'CHAINED_TEST';
0 rows deleted.
这个实验说明了一个问题，10G中，MOVE能解决行迁移的问题。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21158541/viewspace-668402/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/21158541/viewspace-668402/
101
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
