Oracle DBA Daily Experiences

Using dbms_rowid one can map rowid back to object id, file id so the tablespace name and also using the file_id and block_no one can map it back to object_name.

declare
my_rowid rowid := 'AAADYWAABAAAHPaAAB'; /* or any rowid */
rowid_type number;
object_id number;
relative_fno number;
block_no number;
row_no number;
begin
dbms_rowid.rowid_info(my_rowid, rowid_type, object_id, relative_fno, block_no, row_no);
dbms_output.put_line('ROWID: ' || my_rowid);
dbms_output.put_line('Object#: ' || object_id);
dbms_output.put_line('RelFile#: ' || relative_fno);
dbms_output.put_line('Block#: ' || block_no);
dbms_output.put_line('Row#: ' || row_no);
end;
/

Sample Output:
Object#: 13846
RelFile#: 1
Block#: 29658
Row#: 1

Using the object_id you can can map the row id to object_name
SQL> select object_name from dba_objects where object_id = 13846;

OBJECT_NAME
——————————————————————————–
TEST1

Using the relative_fno you can can map it to file name and tablespace_name
TABLESPACE_NAME
——————————
FILE_NAME
——————————————————————————–
SYSTEM
/u01/oradata/TEST/system_01.dbf

Using the file id and block_no one can map it to object_name too
SQL> column segment_name format a30
SQL> select owner, segment_name from dba_extents where file_id = 1 and block_id<= 29658 and block_id + blocks > 29658

OWNER SEGMENT_NAME
—————————— ——————————
SCOTT TEST1