Oracle ROWID学习

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 := i+1;
  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 :