Take the 2-minute tour ×
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.

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.

sys@ORCL>l
  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
sys@ORCL>/

ROWID              OBJECT       FILE      BLOCK    ROW EMPLOYEE_ID FIRST_NAME
------------------ ---------- ---------- ---------- ---------- ----------- --------------------
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

sys@ORCL>
share|improve this question
    
Have you read this? –  Jack Douglas Dec 28 '11 at 17:56
add comment

2 Answers

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? –  Yousui Dec 28 '11 at 17:19
add comment
up vote 1 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;

OWNER    OBJECT_NAME OBJECT_ID  DATA_OBJECT_ID
-------- ----------- ---------- --------------
HR       EMPLOYEES   73953      73321

sys@ORCL>
share|improve this answer
add comment

Your Answer

 
discard

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.