select col_1, col_2, rowid from some_table
A rowid identifies a row in a tableA 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_rowidIn 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_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 : 19
row no : 2
file : D:\ORACLE\DATABASES\ORA10\DATA.DBF
block no: 3890
slot no : 39
[....]
Bigfile rowidsA 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' rowidsAlthough 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. MiscGetting the block number (within a segment from a rowid: select dbms_rowid.rowid_block_number(rowid) from t where ....
|
