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,
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
<p>SQL> select * from  V$version; </p> <p>BANNER <br />-------------------------------------------------------------------------------- <br />Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production <br />PL/SQL Release 11.1.0.6.0 - Production <br />CORE    11.1.0.6.0      Production <br />TNS for 32-bit Windows: Version 11.1.0.6.0 - Production <br />NLSRTL Version 11.1.0.6.0 - Production </p> <p>SQL> select obj#, dataobj#, tab# from tab$ where obj# in ( select object_id from dba_objects where owner='SCOTT' and object_n <br />EMP_C'); </p> <p>      OBJ#   DATAOBJ#       TAB# <br />---------- ---------- ---------- <br />     71135      71135 </p> <p>SQL> truncate table scott.emp_c; </p> <p>Table truncated. </p> <p>SQL> select obj#, dataobj#, tab# from tab$ where obj# in ( select object_id from dba_objects where owner='SCOTT' and object_n <br />EMP_C'); </p> <p>      OBJ#   DATAOBJ#       TAB# <br />---------- ---------- ---------- <br />     71135      71136 </p> <p>SQL> . <br /></p> <p>SQL> alter table scott.emp_c move; </p> <p>Table altered. </p> <p>SQL> select obj#, dataobj#, tab# from tab$ where obj# in ( select object_id from dba_objects where owner='SCOTT' and object_name=' <br />EMP_C'); </p> <p>      OBJ#   DATAOBJ#       TAB# <br />---------- ---------- ---------- <br />     71135      71140</p> |
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!
Hmmm…. Good article Aman. I was also aware ot it 🙂
Thanks for sharing
Nice post .Yes day before yesterday, my friend also asked me what is data object id, i had confused to say him about this. Now i’m cleared this concept.
Mohamed Azar.
Hahaha you are a Guru sir 🙂 .
Thanks Azar !
Nice article.
I want to update my observations.
SQL> select * from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi
PL/SQL Release 10.2.0.4.0 – Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 – Production
NLSRTL Version 10.2.0.4.0 – Production
SQL> create table kgr (no number);
Table created.
SQL> select object_id, data_object_id from user_objects where object_name = ‘KGR’;
OBJECT_ID DATA_OBJECT_ID
———- ————–
488683 488683
SQL> truncate table kgr;
Table truncated.
SQL> select object_id, data_object_id from user_objects where object_name = ‘KGR’;
OBJECT_ID DATA_OBJECT_ID
———- ————–
488683 488683
SQL> insert into kgr values (1);
1 row created.
SQL> roll;
Rollback complete.
SQL> select object_id, data_object_id from user_objects where object_name = ‘KGR’;
OBJECT_ID DATA_OBJECT_ID
———- ————–
488683 488683
SQL> truncate table kgr;
Table truncated.
SQL> select object_id, data_object_id from user_objects where object_name = ‘KGR’;
OBJECT_ID DATA_OBJECT_ID
———- ————–
488683 488684
SQL> alter table kgr move;
Table altered.
SQL> select object_id, data_object_id from user_objects where object_name = ‘KGR’;
OBJECT_ID DATA_OBJECT_ID
———- ————–
488683 488685
Even though i did a truncate without any data, DATA_OBJECT_ID did not change. Only when i inserted one row (and did a rollback to test), then DATA_OBJECT_ID did change after a truncate.
Regards,
Giridhar Kodakalla
Giridhar,
Sounds correct since truncate, on a newly created segment, won’t change anything internally since there is no HWM movement happening. Even when you would insert one row, HWM will “bump up” thus marking few of the blocks as available leading truncate to work over it and eventually new data_object_id will be generated. And that’s correct too since as mentioned, this id is used to point towards the new segment linked with the already existing one.
HTH
Aman….
I’m a friend of the Guru – yours 😉