René Nyffenegger's collection of things on the web | |
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
Oracle's rowid | ||
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
same cluster to have the same rowid.
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 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 : E:\ORACLEXE\ORADATA\XE\USERS.DBF block no: 4924 slot no : 3 row no : 2 file : E:\ORACLEXE\ORADATA\XE\USERS.DBF block no: 4925 slot no : 19 row no : 3 file : E:\ORACLEXE\ORADATA\XE\USERS.DBF block no: 4925 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.
Misc
Getting the block number (within a segment from a rowid:
select dbms_rowid.rowid_block_number(rowid) from t where .... Thanks
Thanks to Michael Hillgruber who notified me of an error on this page.
|