oracle rowid学习-凯发app官方网站

a rowid is a pseudo column (like versions_xid), that uniquely identifies a row within a table, but not within a database. it is possible for two rows of two different tables stored in the same cluster to have the same rowid.

set serveroutput on size 1000000 format wrapped create table rowid_test ( id number, dummy1 varchar2(4000), dummy2 varchar2(4000), dummy3 varchar2(4000), dummy4 varchar2(4000) ); begin for i in 1 .. 400 loop insert into rowid_test values(i, lpad('1', i, '1'), lpad('2', i, '2'), lpad('3', i, '3'), lpad('4', i, '4')); end loop; -- delete but every 20th record delete from rowid_test where mod(id,20) <> 0; end; / declare r rowid; i number := 1; v_file_name dba_data_files.file_name%type; begin for p in (select rowid from rowid_test) loop select file_name into v_file_name from dba_data_files where file_id = dbms_rowid.rowid_to_absolute_fno(p.rowid, user, 'rowid_test'); dbms_output.put_line('row no : ' || i ); dbms_output.put_line(' file : ' || v_file_name); dbms_output.put_line(' block no: ' || dbms_rowid.rowid_block_number(p.rowid)); dbms_output.put_line(' slot no : ' || dbms_rowid.rowid_row_number(p.rowid)); dbms_output.put_line(''); i := i1; end loop; end; / drop table rowid_test;

运行的部分结果：
row no    : 1
file    : d:\oracle9i\oradata\ora09\system01.dbf
block no: 83634
slot no : 19

row no    : 2
file    : d:\oracle9i\oradata\ora09\system01.dbf
block no: 83635
slot no : 1

row no    : 3
file    : d:\oracle9i\oradata\ora09\system01.dbf
block no: 83636
slot no : 4

row no    : 4
file    : d:\oracle9i\oradata\ora09\system01.dbf
block no: 83638
slot no : 0

row no    : 5
file    : d:\oracle9i\oradata\ora09\system01.dbf
block no: 83640
slot no : 1

bigfile rowids

a bigfile tablespace can only have on datafile associated with it, therefor, the three bytes that identified the datafile can now additionally be used to address db blocks.

'changing' rowids

although a rowid uniquely identifies a row in a table, it might change its value if the underlying table is an index organized table or a partitioned table.
also, rowids change if a table is exported and imported using exp/imp.

this implies that rowids should not be stored away for later re-use as the corresponding row then might either not exist or contain completely different data.
misc

getting the block number (within a segment from a rowid:

select dbms_rowid.rowid_block_number(rowid) from t where ....

from :

阅读(1938) | 评论(0) | 转发(0) |

上一篇：linux信号学习

下一篇：oracle rowid格式

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们