Object_ID & Data_Object_ID….

Its just so true that there is so much that is out there which I don’t know about this oracle db software and that holds true even for some very minor things. Almost every day, there is something new that comes in front of me and makes me realize that how less knowledge I have? The same happened today and yesterday as well when I came to know about two new things which I didn’t know before. I am sharing one of them today, the second one would come may be sometime later.

I was asked few days back a question that whether there is anything called data_object_id and if it is, what does it mean? Coincidently, the very same question was asked on OTN forums as well in this thread. I didn’t know what’s the meaning of the data_object_id but it came out that its indeed true that such things exists and is there in the ALL_OBJECTS view as well. Hmm interesting so what it can be? The first search like always was at AskTom which resulted in this thread. According to Tom, the object_id is a unique identifier of the object in the database. Each object is assigned a unique number to recognize it in the database. In the same manner, each object is linked to a segment. The data object id is assigned to that segment and any kind of change done on the physical segment would lead to give a change in that number. Both the numbers are same initially but when the modifications happen on the segment, the data_object_id changes. Both the object_id and data_object_id are the numbers used by oracle to denote the metadata for the object that is used within the data dictionary.

We can see it in action,

SQL> select * from  V$version; 

BANNER 
  
-------------------------------------------------------------------------------- 

  
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production 

  
PL/SQL Release 11.1.0.6.0 - Production 

  
CORE    11.1.0.6.0      Production 

  
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production 

  
NLSRTL Version 11.1.0.6.0 - Production 

SQL> select obj#, dataobj#, tab# from tab$ where obj# in ( select object_id from dba_objects where owner='SCOTT' and object_n 
  
EMP_C'); 

      OBJ#   DATAOBJ#       TAB# 
  
---------- ---------- ---------- 

  
     71135      71135 

SQL> truncate table scott.emp_c; 

Table truncated. 

SQL> select obj#, dataobj#, tab# from tab$ where obj# in ( select object_id from dba_objects where owner='SCOTT' and object_n 
  
EMP_C'); 

      OBJ#   DATAOBJ#       TAB# 
  
---------- ---------- ---------- 

  
     71135      71136 

SQL> . 
  


SQL> alter table scott.emp_c move; 

Table altered. 

SQL> select obj#, dataobj#, tab# from tab$ where obj# in ( select object_id from dba_objects where owner='SCOTT' and object_name=' 
  
EMP_C'); 

      OBJ#   DATAOBJ#       TAB# 
  
---------- ---------- ---------- 

  
     71135      71140

So, that was yet another learning for me. I am sure except me, almost all must be knowing about it already but I didn’t know and to be honest, it feels a little irritating that something so simple and tiny was not clear to me but that’s what makes me believe yet again that the best is to “stay hungry, stay foolish” all the time (courtesy Steve Jobs)  🙂 . And much thanks to Tom Kyte for being there for oracle community and making us understand the complexities of this beast called Oracle db!