ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DBA_OBJECTS中的OBJECT_ID与DATA_OBJECT_ID的区别

DBA_OBJECTS中的OBJECT_ID与DATA_OBJECT_ID的区别

原创 Linux操作系统 作者:sduzjw 时间:2011-03-16 10:54:26 0 删除 编辑

DBA_OBJECTS与X$BH连接时使用的条件是:DBA_OBJECTS.DATA _OBJECT_ID=X$BH.OBJD

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 Uratsuj

 

本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/hayyon/archive/2005/12/29/565190.aspx

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/280958/viewspace-689575/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-06-11

  • 博文量
    33
  • 访问量
    94761