470,638 Members | 1,581 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,638 developers. It's quick & easy.

object_id vs data_object_id in user_objects

I would like to ask for clarification on the meaning of these two
columns. According to the 9i Ref, object_id is the dictionary object
number of the object and data_object_id is the dictionary object number
of the segment that contains the object.

First question, what's the difference? Sorry to sound philosophic here,
but how could an object exist without a segment? Obviously it does, but
I could sure use some description of how to interpret the difference.

Second question,

Scenario:

Insert and commit two rows in a table.

Query user_objects for the table, object_id and data_object_id have the
identical value: 31264.

Delete both rows and commit, the columns still have identical values.

Reinsert the rows, commit and truncate the table, object_id is still
31264, data_object_id is now 31265.

Maybe the answer to my first question will clear this up, but at this
point I'm baffled about how the "style" of removing rows from a table
can cause the table's segment id to change? Isn't it the same segment?

Thanks for any illumination anyone can throw.

Jack

Jul 19 '05 #1
2 9063
The data object id is assigned at creation time and does not change
unless the object is dropped and recreated. The data_object_id is also
assigned at creation time but over time the segment used to store the
object can be changed.

Observe
UT1 > select object_id, data_object_id from dba_objects
2 where object_name = 'MARKTEST_IDX1';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
31698 31699

UT1 > alter index marktest_idx1 rebuild;

Index altered.

UT1 > select object_id, data_object_id from dba_objects
2 where object_name = 'MARKTEST_IDX1';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
31698 31980

The data_object_id changed to reflect the new segment used to house the
index.

run this query
select owner, object_name, object_type, object_id, data_object_id
from dba_objects
where object_id <> data_object_id

And take a look at what turns up.

HTH -- Mark D Powell --

Jul 19 '05 #2
Thanks Mark.

My follow up question is why truncating the table resulted in a new
segment but deleting rows and committing didn't. I think that what I am
sniffing around is something I've seen referred to as the "highwater
mark". If that's related, can you recommend a good ref so I can pursue
this on my own? I have read what the 9i Concepts book has to say about
this, but it didn't help.

Thanks again.

p.s. I used to work for EDS at the Camp Hill data center a long time
ago.

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Philippe LAVIGERIE | last post: by
1 post views Thread by deepu03 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.