Table Name from Rowid

In this section we will learn about .

How we can get the Table Name from Rowid?

Before going to get the answer of the above question lets have a quick review on Rowid.

ROWID:
  • Rowid is a pseudocolumn that returns the address of the row.
  • A rowid value uniquely identifies a row in the database.
  • The rowid is intended to be immutable (that is, unchangeable) and the user should not try to change it once it is assigned.
In the above image the rowid return by the dual table is :

AAAABzAABAAAAMiAAA .

Six positions for the data object number .  
Three positions for the relative file number.   
Six positions for the block number .   
Three positions for the row number.

Now , Let back track and get the name of given rowid for example .

There are so many ways to get the Table name from Rowid . 
So, from the above discussion we get the conclusion that  rowid return the address of row and rows are stored in table and table is database object .
So if we get the all table name and scan for rowid then we will get the answer.

Let gets start .
Line 3: created cursor for collecting the table name .
Line 8: Started the loop to access the table one by one for searching the rowid.
Line 16: Condition checking for exists the rowid in table or not .

The other way is to use oracle in built function 

  • Get the object id from rowid using DBMS_ROWID.rowid_object() function.
  • Then collect the object id information from dba_objects view.










I am interested in hearing your feedback about this question & answer, so that I can improve my lab series on sql for you.connect with us on facebooktwitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

2 comments:

  1. Nice .
    Is there any other way to get table name from rowid

    ReplyDelete
    Replies
    1. Hi Amitabh,

      There are two method i mentioned one using plsql and second is without using plsql .You can go with any one of them .

      Regards,
      Abhishek.

      Delete