What is the difference between object_id and data_object_id?

If you describe the dba_objects, you will find two IDs called: object_id & data_object_id, so , what is the difference between them?

object_id is the primary key. Any object will have object_id irrespective, if it has an associated segment or not. For example, sequence, package, synonym are all objects with object_id but without any physical segments.

Initially, when you create and object (like table), both the object_id and data_object_id are the same, but there are some operations that change the data_object_id, like:

truncate table;
alter table move;
alter table exchange partition;
in case the table is part of a cluster, the data_object_id will point to the cluster segment.

So, we can assume that the data_object_id is logically a segment_id, and if the segment changed physically, the segment_id will be changed also.

Let us have some examples:

So, when we create a table, initially, both IDs will be the same. If we truncated the table, the data_object_id will be changed. If we moved the table, also the data_object_id will be changed.
For partitioned table, the table itself has no associated segment, so, the data_object_id is null. Each partition will initially has both object_id & data_object_id equal.
If you exchanged a table with a table partition, also, the data_object_id will be exchanged.
when you create a cluster, the cluster and its child tables will be assigned the same data_object_id, although each one of them has its own object_id

A final remark, buffers at the buffer cache are associated with segments not objects.
SELECT COUNT (*) FROM v$bh bc, user_objects obj WHERE bc.objd = obj.data_object_id AND object_name = ‘TT1’;

The above examples are against database version 12.2 (Oracle Database 12c Enterprise Edition Release – 64bit Production). You may get different results from different or old versions.

Donate to support our blogging work




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s