Finding the location of a row in the database

Finding the location of a row in the database

will learn on how to check in which datafile (irrespective of any tablespace in the database) a row is existing i.e we are finding the location of a row in the database.

This is possible through DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO package and ROWID of a row. We all already know that ROWID is a unique identifier assigned by oracle to each and every row in the database and it is a hidden column in a table i.e every table will have a psuedo column called ROWID, but it will not be shown when we describe a table.

Let’s take a quick example

SQL> conn scott/tiger
Connected.

SQL> select rowid, DEPTNO, DNAME, LOC from dept;

ROWID                  DEPTNO DNAME          LOC
—————— ———- ————– ————-
AAAR3qAAEAAAACHAAA         10 ACCOUNTING     NEW YORK
AAAR3qAAEAAAACHAAB         20 RESEARCH       DALLAS
AAAR3qAAEAAAACHAAC         30 SALES          CHICAGO
AAAR3qAAEAAAACHAAD         40 OPERATIONS     BOSTON

SQL>
SQL> SELECT dbms_rowid.rowid_to_absolute_fno(‘AAAR3qAAEAAAACHAAD’, ‘SCOTT’, ‘DEPT’) FILEID FROM DUAL;

    FILEID
———-
         4

SQL> disc
SQL>
SQL> conn / as sysdba

SQL> SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_DATA_FILES WHERE FILE_ID=4;

TABLESPACE_NAME
——————————
FILE_NAME
——————————————————————————–
USERS
/U01/ORA10G/PROD/USERS01.DBF