dba_objects中object_id与data_object_id的区别

今天在看由盖国强等大牛写的《oracle DBA手记2》，在看到崔华利用AWR报告的诊断案例时里面提到，查看object_id与data_object_id是否一致，如果一致说明表近期没有被truncate过。此处看不懂为什么，so google之，查查data_object_id是个什么东东，平时还真没注意过。

    在dba_objects等视图里面有两个比较容易搞混的字段object_id和data_object_id
这两个字段基本上有什么大的区别呢?
object_id其实是对每个数据库中数据对象的唯一标识
data_object_id用的相比来说会少一些,主要是和seg$对应,用来表示object的物理存储段的实际位置.
只有表,索引,undo这些有实际物理存储位置的对象才有data_object_id,而像一些函数,存储过程,以及view等等是没有data_object_id的,也就是说仅有object_id

来看个实验就一目了然了:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> create table test as select * from dual;

表已创建。

SQL>select object_id,DATA_OBJECT_ID from user_objects where object_name='ZYH_TEM';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     48498          48498
    
SQL>INSERT INTO ZYH_TEM SELECT * FROM ZYH;

194 rows created.

SQL>COMMIT;

Commit complete.

SQL>select object_id,DATA_OBJECT_ID from user_objects where object_name='ZYH_TEM';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     48498          48498

SQL>TRUNCATE TABLE ZYH_TEM;

Table truncated.

SQL>select object_id,DATA_OBJECT_ID from user_objects where object_name='ZYH_TEM';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     48498          48830
    
可以看到经过truncate后两个id不一样了

SQL>create table ttt  as select * from zyh where 1=2;

Table created.

SQL>select object_id,DATA_OBJECT_ID from user_objects where object_name='TTT';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     48831          48831

SQL>SELECT COUNT(*) FROM TTT;

  COUNT(*)
----------
         0

SQL>TRUNCATE TABLE TTT;

Table truncated.

SQL>select object_id,DATA_OBJECT_ID from user_objects where object_name='TTT';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     48831          48831

SQL> create view view_test as select * from test;

视图已创建。

SQL> select object_id,data_object_id from user_objects where object_name='VIEW_TEST';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    874947

这里可以看到具有物理存储段的对象table是存在data_object_id,而view就没有.

根据上面的实验我们可以看到,object_id和data_object_id的一些微妙的关系,大部分的对象在一般情况下,这两个值是相等的.
当创建一个新的对象的时候object_id和data_object_id应该分别取
max(select max(object_id) from dba_objects)+1,
max(select max(hwmincr) from seg$)+1
但是在个别情况下两个值会不同,比如我们
进行truncate,move等操作.

 

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

转载于:http://blog.itpub.net/23891491/viewspace-748945/