Oracle‘s rowid

2024-08-29 13:43:53

字体：大中小

来源：转载

供稿：网友

select col_1, col_2, rowid from some_table

A rowid identifies a row in a table

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

Using dbms_rowid

In order to find the datafile, block number and slot number, dbms_rowid can be used: set serveroutput on size 1000000 format wrappedcreate 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_filename dba_data_files.file_name%type;begin for p in ( select rowid from rowid_test ) loop select file_name into v_filename from dba_data_files where file_id = dbms_rowid.rowid_relative_fno(p.rowid); dbms_output.put_line('row no : ' i ); dbms_output.put_line(' file : ' v_filename); 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:/Oracle/DATABASES/ORA10/DATA.DBF block no: 3890 slot no : 19row no : 2 file : D:/ORACLE/DATABASES/ORA10/DATA.DBF block no: 3890 slot no : 39 [....]

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.