Object# VS Data_Object#
在user_objects等视图里面有两个比较容易搞混的字段object_id和data_object_id
这两个字段基本上有什么大的区别呢?
object_id其实是对每个数据库中数据对象的唯一标识
data_object_id用的相比来说会少一些,主要是和seg$对应,用来表示object的物理存储段的实际位置.
只有表,索引,undo这些有实际物理存储位置的对象才有data_object_id,而像一些函数,存储过程,以及view等等是没有data_object_id的,也就是说仅有object_id
既然有了object_id那还要data_object_id干吗？ 其实object_id和data_object_id同样是表示数据库对象的一个唯一标志，但是object_id表示的是逻辑id，data_object_id表示的是物理id。如果一些object没有物理属性的话那它就不存在data_object_id，例如procedure,function,package,data type,db link,mv定义,view定义,临时表,分区表定义等等这些object都是没有对应着某个segment，因此它们的data_object_id都为空。 当表刚创建的时候它的object_id和data_object_id都是相等的，但是如果表经过move或truncate后那么data_object_id将会有变化。
来看个实验就一目了然了:
C:\>sqlplus /nolog
SQL*Plus: Release 10.2.0.3.0 - Production on 星期四 2月 14 11:03:41 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> conn / as sysdba;
已连接。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL> create table test as select * from dual;
表已创建。
SQL> select object_id,data_object_id from user_objects where object_name='TEST';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
56278 56278
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
---------- --------------
56279
这里可以看到具有物理存储段的对象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等操作.
看一下下面的实验,就利用我们刚刚创建好的test表来试图说明这个问题:
SQL> select object_id,data_object_id from user_objects where object_name='TEST';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
56278 56278
SQL> truncate table test;
表被截断。(注意这个有内容的情况下才会改变data_object_id)
SQL> select object_id,data_object_id from user_objects where object_name='TEST';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
56278 56279
File# VS Relative_File# ：
Oracle Database assigns each datafile two associated file numbers, an absolute file number and a relative file number, that are used to uniquely identify it
Absolute：Uniquely identifies a datafile in the database. This file number can be used in many SQL statements that reference datafiles in place of using the file name. The absolute file number can be found in the FILE# column of the V$DATAFILE or V$TEMPFILE view, or in the FILE_ID column of the DBA_DATA_FILES or DBA_TEMP_FILES view
Relative：Uniquely identifies a datafile within a tablespace. For small and medium size databases, relative file numbers usually have the same value as the absolute file number. However, when thenumber of datafiles in a database exceeds a threshold (typically 1023), the relative file number differs from the absolute file number. In a bigfile tablespace, the relative file number is always 1024 (4096 on OS/390 platform).
-------- 文章来自互联网，如侵犯了您老的权利请告知本人，第一时间去除此文
