DBA_OBJECTS object_id data_object_id

OBJECT_ID   Dictionary object number of the object

DATA_OBJECT_ID  Dictionary object number of the segment that contains the object

Note: OBJECT_ID and DATA_OBJECT_ID display data dictionary metadata. Do not confuse these numbers with the unique 16-byte object identifier (object ID) that Oracle assigns to row objects in object tables in the system.

Tom:

The object_id is the primary key, the data_object_id is the foreign key to the
data segment.

Initially they are "the same"

But any operation that radically changes the data segment - eg: truncate, alter
table exchange partition, etc -- will change the data_object_id -- the data
segment the object points to.


consider:

ops$tkyte@ORA9IR2> create table t ( x int );

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select object_name, object_id, data_object_id
  2    from user_objects
  3   where object_name = 'T';

OBJECT_NAME                     OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
T                                   29413          29413

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> truncate table t;

Table truncated.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select object_name, object_id, data_object_id
  2    from user_objects
  3   where object_name = 'T';

OBJECT_NAME                     OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
T                                   29413          29414

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t move;

Table altered.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select object_name, object_id, data_object_id
  2    from user_objects
  3   where object_name = 'T';

OBJECT_NAME                     OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
T                                   29413          29415

returned by dbms_rowid is data_object_id not object_id

object_id is logical id

data_object_id is physical id

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

from: http://performance-insight.com/html/ora3/back/Oracle9i_12.html

Object ID
Object ID is a number to uniquely identify Oracle object. In previous issues, we analyzed X$BH to examine object on database buffer. X$BH also contains a column to uniquely identify an object.

I executed following SQL to analyze database buffer several times.

SQL> select 2 o.object_name, blsiz , count(*) blocks , lru_flag , tch, state 3 from x$bh b , dba_objects o 4 where b.obj = o.object_id 5 and o.object_name in ('BLOCK4','BLOCK4_IDX') 6 group by b.blsiz, o.object_name, lru_flag, tch, state;

I have to confess that this SQL is not quite correct.

Table and index exist as follows, for example.

# Find an object starting with SUMMER SQL> select object_id, data_object_id, object_name from dba_objects 2 where object_name like 'SUMMER%'; OBJECT_ID DATA_OBJECT_ID OBJECT_NAME ---------- -------------- -------------------- 43064 43064 SUMMER 43065 43065 SUMMER_IDX SUMMER_IDX is an index in SUMMER table.

OBJECT_ID and DATA_OBJECT_ID are exactly the same. Of course, corresponding object numbers on database buffer are exactly the same.

SQL> select obj, count(1) from x$bh 2 where obj in (43064,43065,43066,43067) 3 group by obj; OBJ COUNT(1) ---------- ---------- 43064 16 43065 21 *As DBJ condition, previous object numbers and other two numbers are specified. I will talk about these two numbers (43066, 43067) later.

Now, I truncate this object (SUMMER) to examine how object number is changed.

SQL> truncate table summer; Table truncated SQL> select object_id, data_object_id, object_name from dba_objects 2 where object_name like 'SUMMER%'; OBJECT_ID DATA_OBJECT_ID OBJECT_NAME ---------- -------------- -------------------- 43064 43067 SUMMER 43065 43066 SUMMER_IDX

By truncating a table, DATA_OBJECT_ID of an index in a table is changed.
DATA_OBJECT_ID is an ID to manage object version. Every time, TRUNCATE is executed, this number increases. The largest number (OBJECT_ID or DATA_OBJECT_ID) + 1 is a number to be allocated.

After TRUNCATE, dictionary is altered. However, actual data is not deleted. This means that information in regard to ROLLBACK and REDO is not created in each row of a table. If there is a large number of data, performance is quite fast. However, this doesn't mean old data is deleted.
Such process, version management, is therefore necessary. OBJ column in X$BH indicates DATA_OBJ_ID.

Remarkable thing is that DATA_OBJECT_ID of an index is smaller. I assume that after index is truncated, table is also truncated.

In addition, as package doesn't need to manage version, DATA_OBJ_ID is NULL.

SQL> select object_name, object_id, data_object_id, object_type from dba_objects 2 where object_type='PACKAGE' OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE ------------------------------ ---------- -------------- ------------------ HTTP_EXP 19168 PACKAGE HTTP_SECURITY_CASCADE 19150 PACKAGE IFR_EXP 19169 PACKAGE IFR_PACKAGE 19082 PACKAGE INITJVMAUX 5214 PACKAGE IST_ENGINE90 23788 PACKAGE . .

OBJECT_ID is not changed unless it is dropped. OK, let's take a look at the status of database buffer after TRUNCATE.

SQL> select obj, count(1) from x$bh 2 where obj in (43064,43065,43066,43067) 3 group by obj; OBJ COUNT(1) ---------- ---------- 43066 1 43067 3

This is not a value in OBJECT_ID column in DBA_OBJECT, but DATA_OBJECT_ID. I specify 43066 and 43067 as OBJ condition in SQL because I estimated that one would be added to DATA_OBJ_ID.

That's it for today.

Yoshihiro Uratsuji