Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to know how to use the object number that extract from rowid column? It's seems that this oracle number is not the same as object_id in dba_objects view. Anyone can help me with it please? Thanks.

  1  SELECT rowid, DBMS_ROWID.Rowid_object(ROWID)   "OBJECT",
  2     DBMS_ROWID.Rowid_relative_fno(ROWID) "FILE",
  3     DBMS_ROWID.Rowid_block_number(ROWID) "BLOCK",
  4     DBMS_ROWID.Rowid_row_number(ROWID)   "ROW",
  5     employee_id, first_name
  6  FROM   hr.employees
  7* WHERE  department_id = 30

------------------ ---------- ---------- ---------- ---------- ----------- --------------------
AAAR5pAAFAAAADPAAO  73321          5    207     14         114 first_name
AAAR5pAAFAAAADPAAP  73321          5    207     15         115 first_name
AAAR5pAAFAAAADPAAQ  73321          5    207     16         116 first_name
AAAR5pAAFAAAADPAAR  73321          5    207     17         117 first_name
AAAR5pAAFAAAADPAAS  73321          5    207     18         118 first_name
AAAR5pAAFAAAADPAAT  73321          5    207     19         119 first_name

6 rows selected.

sys@ORCL>select * from dba_objects where object_id = 73321;

no rows selected

share|improve this question
Have you read this? – Jack Douglas Dec 28 '11 at 17:56

An *object_id* identifies an object (such as a table, or an index) within Oracle.

The rowid can only be used for tables, or more specifically: the records within tables. It uniquely identifes such a record, that is, where the record can be found on the harddisk.

Therefore, the function *DBMS_ROWID.Rowid_relative_fno* gives you an id that identifies the file, *DBMS_ROWID.Rowid_block_number* gives you the block within that file and *DBMS_ROWID.Rowid_row_number* gives you the "row offset" within that block of the actual record.

In your case, you can query v$datafile with the file_id of 5 to find out which file "holds" the record. select name from v$datafile where file# = 5.

share|improve this answer
What does the DBMS_ROWID.Rowid_object mean? – Ogrish Man Dec 28 '11 at 17:19
up vote 2 down vote accepted

I found the answer of my own question. The so called object number from DBMS_ROWID.Rowid_object corresponds to dba_objects.data_object_id, not object_id.

sys@ORCL>select owner, object_name, object_id, data_object_id from dba_objects where data_object_id = 73321;

-------- ----------- ---------- --------------
HR       EMPLOYEES   73953      73321

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.