实验出发点：
今天做关于flush buffer cache和x$bh关系的时候，发现的一个问题，详情可以请看http://blog.itpub.net/30174570/viewspace-2140062/。

其他资料：
正如文章题目而言，两者有什么区别？

从http://blog.itpub.net/12361284/viewspace-170223/可以得到以下信息：
1.object_id其实是对每个数据库中数据对象的唯一标识
2.data_object_id用的相比来说会少一些,主要是和seg$对应,用来表示object的物理存储段的实际位置.
只有表,索引,undo这些有实际物理存储位置的对象才有data_object_id,而像一些函数,存储过程,以及view等等是没有data_object_id的,也就是说仅有object_id

另外，当创建一个新的对象的时候object_id和data_object_id应该分别取
max(select max(object_id) from dba_objects)+1,
max(select max(hwmincr) from seg$)+1

环境说明：
1.操作系统版本

[oracle@oracle ~]$ uname -a
Linux oracle.example.com 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
[oracle@oracle ~]$ lsb_release -a
LSB Version: :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
Distributor ID: RedHatEnterpriseServer
Description: Red Hat Enterprise Linux Server release 6.5 (Santiago)
Release: 6.5
Codename: Santiago

2.数据库版本

SYS@proc> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

实验过程：
但是自己做实验还是有些出入的。
1.关于这两者的取值的实验如下：

SYS@proc> select a.max_object_id,b.max_hwmincr from (select max(object_id) max_object_id from dba_objects) a,(select max(hwmincr) max_hwmincr from seg$) b;
MAX_OBJECT_ID MAX_HWMINCR
------------- -----------
89219 89219
SYS@proc> create table pc(id number);
Table created.
SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='PC';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
89220 89220 --符合前文所说的创建新对象时候的取值规则
SYS@proc>
SYS@proc> drop table pc purge;
Table dropped.
SYS@proc> select a.max_object_id,b.max_hwmincr from (select max(object_id) max_object_id from dba_objects) a,(select max(hwmincr) max_hwmincr from seg$) b;
MAX_OBJECT_ID MAX_HWMINCR
------------- -----------
89219 89219
SYS@proc> create table pc(id number);
Table created.
SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='PC';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
89221 89221 --不符合前文所说的创建新对象时候的取值规则

根据大量实验发现，创建的对象的object_id和data_object_id取值会取MAX_OBJECT_ID和MAX_HWMINCR曾经达到的最大值+1，也就是上述实验中一开始pc表的object_id,data_object_id均为89220，但是drop表pc之后，89220数值并不能重用，新创建的对象只能取89220+1的数值。具体原因还不知道，这是接下去可以探究的问题之一。

从其他实验结果来看。特别地，当上述实验中的MAX_OBJECT_ID和MAX_HWMINCR不等的时候，新创建对象的object_id,data_object_id值为MAX_OBJECT_ID和MAX_HWMINCR其中比较大一个的曾经最大值+1。具体原因还不知道，这是接下去可以探究的问题之一。
2.看不明白可以直接看如下实验：

SYS@proc> create table aa (id number);
Table created.
SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='AA';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
89224 89224
SYS@proc> alter table aa move tablespace test;
Table altered.
SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='AA';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
89224 89225
SYS@proc> select a.max_object_id,b.max_hwmincr from (select max(object_id) max_object_id from dba_objects) a,(select max(hwmincr) max_hwmincr from seg$) b;
MAX_OBJECT_ID MAX_HWMINCR
------------- -----------
89224 89225
SYS@proc> create table yy(id number);
Table created.
SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='YY';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
89226 89226

从这里也可以看出，对表move操作会增加DATA_OBJECT_ID的值，具体原因还不知道，这是接下去可以探究的问题之一。

3.从网上资料看，truncate操作也有同样的效果。
不过自己实验发现，对非空表做截断才会。具体原因还不知道，这是接下去可以探究的问题之一。

SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='YY';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
89226 89226
SYS@proc> select count(*) from yy;
COUNT(*)
----------
0
SYS@proc> truncate table yy;
Table truncated.
SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='YY';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
89226 89226
SYS@proc> insert into yy values(1);
1 row created.
SYS@proc> commit;
Commit complete.
SYS@proc> truncate table yy;
Table truncated.
SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='YY';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
89226 89227

实验结论：
具体结论前文可以明显看出来了，比较懒就不写了。
关于data_object_id的意义，待文中未解决的问题解决之后可能才会明白。
要是各位网友有知道的，欢迎指教，先谢谢。

ITPub博客

Oracle中object_id和data_object_id的区别

最新文章