Tony’s Oracle Tips

February 21, 2010

What is the difference between OBJECT_ID and DATA_OBJECT_ID?

Filed under: Uncategorized — tonyhasler @ 1:12 pm

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!

Advertisements

12 Comments »

  1. Never stopped to consider why data_object_id instead of object_id was used to refer to v$bh. Thank you.

    Comment by oracledummy — February 22, 2010 @ 4:17 am | Reply

  2. Excellent explanation

    Thank you

    Comment by Kostas Hairopoulos — March 20, 2010 @ 6:47 pm | Reply

  3. Very nicely explained

    Thank you

    Comment by Arjun — March 30, 2010 @ 7:07 am | Reply

  4. really good explanation. not sure how many other operations can change data_object_id i.e like exchange,move,drop etc.
    Thanks

    Comment by Ananda — September 3, 2010 @ 10:29 am | Reply

    • Exchange partition, alter … move, and index rebuild are the only three I can think of. Anybody know of other operations that can do this?

      Comment by tonyhasler — September 4, 2010 @ 11:40 pm | Reply

      • i am just thinking about operations by DBMS_REDEFINITION package. It may change the data_object_id’s but nor sure.
        Thanks

        Comment by raova — October 5, 2010 @ 11:35 am

      • Truncate will do it as well.

        Comment by Ajaykumar Gupta — January 29, 2015 @ 6:58 am

      • Ajaykumar,

        Yes context variables would work as would package state variables (most of the time). But as I said in response to the last comment the user of the view was not a sophisticated user and was using a data visualization tool that, like many tools offering access to various types of data source, has no ability to use Oracle specific features such as context variables.

        However, thanks for your interest in my blog.

        –Tony

        Comment by tonyhasler — June 10, 2015 @ 6:34 am

  5. I have tried DBMS_REDEFINITION package to move a table from one tablespace to another tablespace.

    The object_id and data_object_id both changed to object_id and data_object_id of intermediate table.

    I have enabled 10053 trace to see what actually hapening.

    It is using INSERT INTO .. SELECT statement to move data form original table to intermediate table.

    After that looks like it is renaming the table.

    scott@test> CREATE TABLE t1 (a INTEGER) tablespace tools;

    Table created.

    scott@test> SELECT object_name,object_id, data_object_id FROM user_objects WHERE object_name = ‘T1′;

    OBJEC OBJECT_ID DATA_OBJECT_ID
    —– ———- ————–
    T1 35045782 35045782

    scott@test>col object_name for a25
    scott@test>col SEGMENT_NAME for a35
    scott@test>col TABLESPACE_NAME for a25

    scott@test> select segment_name,tablespace_name from user_segments where segment_name=’T1′;

    SEGMENT_NAME TABLESPACE_NAME
    ———————————– ————————-
    T1 TOOLS

    scott@test> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(uname=>’scott’,tname=>’T1′,options_flag=>dbms_redefinition.cons_use_rowid);

    PL/SQL procedure successfully completed.

    scott@test> CREATE TABLE scott.T2 TABLESPACE USERS AS SELECT * FROM T1 WHERE 1=2;

    Table created.

    scott@test> SELECT object_name,object_id, data_object_id FROM user_objects WHERE object_name = ‘T2′;

    OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
    ————————- ———- ————–
    T2 35045783 35045783

    scott@test> select segment_name,tablespace_name from user_segments where segment_name=’T2’;

    SEGMENT_NAME TABLESPACE_NAME
    ———————————– ————————-
    T2 USERS

    scott@test>alter session set events ‘10053 trace name context forever, level 12′;

    scott@test> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(uname=>’scott’,orig_table=>’T1′,int_table=>’T2′,options_flag=>dbms_redefinition.cons_use_rowid);

    PL/SQL procedure successfully completed.

    scott@test> scott@test> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname=>’scott’, orig_table=>’T1′, int_table=>’T2′);

    PL/SQL procedure successfully completed.

    scott@test> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>’scott’, orig_table=>’T1′, int_table=>’T2′);

    PL/SQL procedure successfully completed.

    scott@test>alter session set events ‘10053 trace name context off’;

    scott@test> SELECT object_name,object_id, data_object_id FROM user_objects WHERE object_name in (‘T1′,’T2’);

    OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
    ————————- ———- ————–
    T1 35045783 35045783
    T2 35045782 35045782

    nwcsprd_service@SSUS> select segment_name,tablespace_name from user_segments where segment_name in (‘T1′,’T2’);

    SEGMENT_NAME TABLESPACE_NAME
    ———————————– ————————-
    T1 USERS
    T2 TOOLS

    scott@test>

    trace entry is as follows.

    sql_id=c65p9bu3a32qm.
    Current SQL statement for this session:
    INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND */ INTO “SCOTT”.”T2″(M_ROW$$,”A”) SELECT “T1″.”ROWID”,”T1″.”A” FROM “SCOTT”.”T1″ “T1”
    —– PL/SQL Call Stack —–
    object line object
    handle number name
    c9bbb3118 50 package body SYS.DBMS_REDEFINITION
    c9bbb3118 1343 package body SYS.DBMS_REDEFINITION
    ae7249bc8 1 anonymous block

    so in a way it changes object_id and data_object_id of an object but not similar to ‘EXCHANGE PARTITIONS’,’ALTER TABLE … MOVE’ and ‘ALTER INDEX …REBUILD’ operations.

    Thanks
    Ananda

    Comment by raova — October 5, 2010 @ 2:27 pm | Reply

    • Ananda,

      A nice comment that further clarifies the issue. Yes, this is different from EXCHANGE PARTITION in that with DBMS_REDEFINITION it is the OBJECT_ID that is swapped and not the DATA_OBJECT_ID. At first glance this seems like two different ways to accomplish the same thing. I would guess that the difference arises because of references in the data dictionary to the object ids that need to be left undistrurbed.

      Comment by tonyhasler — October 9, 2010 @ 12:48 pm | Reply

  6. Thank you. Good explanation

    Comment by Vinay — November 14, 2010 @ 7:00 pm | Reply

  7. […] obj and objn areas are important because those contains object id and data object id. What is difference between object id and data object id? (https://tonyhasler.wordpress.com/2010/02/21/what-is-the-difference-between-object_id-and-data_object_&#8230😉 […]

    Pingback by Dump Data Block for Finding Object ID and Name | Oracle Board | Fatih Bulut — March 6, 2014 @ 12:34 pm | Reply


RSS feed for comments on this post. TrackBack URI

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 )

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: