You may have noticed that the views DBA_OBJECTS, ALL_OBJECTS and USER_OBJECTS contain two identification columns: OBJECT_ID and DATA_OBJECT_ID. They are described in the documentation as the “Dictionary object number of the object” and the “Dictionary object number of the segment that contains the object”. I found this confusing. Particularly as neither the view DBA_SEGMENTS nor the underlying dictionary table SEG$ contain any identifier. This blog is the results of my research to understand this better.
To make this blog more readable I will use the term segment id to refer to the DATA_OBJECT_ID. This is unofficial terminology but is easier to read than the dictionary object number of the segment that contains the object.
I am sure that anybody keen enough to read my blog knows the difference between a segment and a dictionary object. A segment refers to one or more extents that represent actual storage. Some dictionary objects require storage and some do not. SEQUENCE, PACKAGE, and TYPE BODY are examples of dictionary objects that do not require storage and so have no associated segment. For these cases the column DATA_OBJECT_ID will be either NULL or zero. There is some inconsistency here but otherwise this is straightforward.
Let us begin our investigation of objects that require storage with a simple example using 11gR1 (in 11gR2 the segment is not allocated until the first row is inserted)
COLUMN object_name format a5 COLUMN subobject_name format a5 CREATE TABLE t1 (a INTEGER); SELECT object_id, data_object_id FROM user_objects WHERE object_name = 'T1';
The result of these operations is as follows:
OBJECT_ID DATA_OBJECT_ID ---------- -------------- 85495 85495
We can see that the two identifiers returned are the same in this case. This may fool you into believing that there is some meaningful correlation between the two numbers but as I have discovered there is not. It is the case that numbers are allocated to objects and segments using the same “number fountain” but as we shall see they need not be the same.
Let us take another case:
CREATE TABLE t2 (a INTEGER) PARTITION BY LIST(a) (PARTITION p1 VALUES (1),PARTITION p2 VALUES (2)); SELECT object_name, subobject_name, object_id, data_object_id FROM user_objects WHERE object_name = 'T2';
The result is of the query is:
OBJEC SUBOB OBJECT_ID DATA_OBJECT_ID ----- ----- ---------- -------------- T2 85496 T2 P1 85497 85497 T2 P2 85498 85498
We can see that the table itself has no segment and that the object id and segment id of the partitions are the same as before.
ALTER TABLE t1 MOVE; SELECT object_name, subobject_name, object_id, data_object_id FROM user_objects WHERE object_name = 'T1';
After moving the table the results of the query are:
OBJEC SUBOB OBJECT_ID DATA_OBJECT_ID ----- ----- ---------- -------------- T1 85495 85499
We can see that although the object id has remained unchanged the newly allocated segment has been allocated a new segment id.
CREATE TABLE t3 (a INTEGER); SELECT object_name, subobject_name, object_id, data_object_id FROM dba_objects WHERE object_name = 'T3' AND owner = USER; ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t3; SELECT object_name, subobject_name, object_id, data_object_id FROM user_objects WHERE object_name IN ('T2', 'T3');
The test above creates a new table T3 and then exchanges it with partition P1 of T2. This is what happens:
OBJEC SUBOB OBJECT_ID DATA_OBJECT_ID ----- ----- ---------- -------------- T3 85500 85500 1 row selected. Table altered. OBJEC SUBOB OBJECT_ID DATA_OBJECT_ID ----- ----- ---------- -------------- T2 85496 T2 P1 85497 85500 T2 P2 85498 85498 T3 85500 85497
You see what has happened? The exchange partition simply swapped the segments around. Now we have one object with a dictionary id of 85500 and another object that is associated with segment id 85500. Any idea that there is some correlation between the numbers now seems lost.
So what is the point of the segment id? Why has Oracle exposed this column to us when we cannot correlate it with DBA_SEGMENTS? Well, there are two purposes that I have discovered.
CREATE CLUSTER c1 (a INTEGER); CREATE TABLE t4 (a INTEGER)CLUSTER c1(a); CREATE TABLE t5 (a INTEGER)CLUSTER c1(a); SELECT object_name, object_id, data_object_id FROM user_objects WHERE object_name IN ('C1', 'T4', 'T5');
Here we have created a cluster and allocated two tables to the cluster. This is the output of the query :
OBJEC OBJECT_ID DATA_OBJECT_ID ----- ---------- -------------- C1 85501 85501 T4 85502 85501 T5 85503 85501
We can see that in the case of clusters more than one object can be associated with a single segment and we can use the segment id to identify all the objects that share a segment.
Another reason we may need the segment id is when we are examining the buffer cache.
SELECT COUNT (*) FROM v$bh b, user_objects o WHERE b.objd = o.data_object_id AND object_name = 'T1';
Buffers in the cache are associated with segments and not objects. This distinction is required for two reasons. The first is that in the case of clusters a single buffer is associated with multiple objects. The second is that if we correlated buffers with objects then an EXCHANGE PARTITION operation would require all of the buffers associated with the objects being exchanged to be renumbered.
In fact, it was a query similar to the above that prompted this analysis. I had incorrectly joined the OBJD column of V$BH with OBJECT_ID from DBA_OBJECTS. Because there were so many objects that had identical values for the segment id and the object id my query looked like it was working fine!