Finding the location of a row in the database
Posted by Pavan DBA on March 27, 2012
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
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> SELECT dbms_rowid.rowid_to_absolute_fno(‘AAAR3qAAEAAAACHAAD’, ‘SCOTT’, ‘DEPT’) FILEID FROM DUAL;
SQL> conn / as sysdba
SQL> SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_DATA_FILES WHERE FILE_ID=4;
So, isn’t easy guys to find in which tablespace a row is existing 🙂
Hope this helps…. HAPPY LEARNING