Pavan DBA's Blog

Friends, nice to write an article after really long time….

Earlier I posted an article on how to “How to identify in which datafile (of same tablespace) object is existing”. see that article here  – https://pavandba.com/2010/04/23/in-which-datafile-object-resides/

But in this article we 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

So, isn’t easy guys to find in which tablespace a row is existing 🙂

Hope this helps…. HAPPY LEARNING