patternMinor
Question on Oracle rowid column in every table
Viewed 0 times
columneveryoraclequestiontablerowid
Problem
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>Solution
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>Code Snippets
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>Context
StackExchange Database Administrators Q#9882, answer score: 4
Revisions (0)
No revisions yet.