oracle 24365,深入解析：DBA_OBJECTS中的OBJECT_ID与DATA_OBJECT_ID的区别

最新推荐文章于 2021-04-09 14:37:51 发布

weixin_39789042

最新推荐文章于 2021-04-09 14:37:51 发布

阅读量458

点赞数

文章标签： oracle 24365

墨墨导读：在Oracle dba_objects视图中存在object_id和data_oubject两个列，已经存在object_id列了为什么还会有一个data_object_id列呢，这两个列有什么区别？通过本文了解一下。object_id和data_object_id同样是表示数据库对象的一个唯一标志，但是object_id表示的是逻辑id，data_object_id表示的是物理id。只有表,索引,undo这些有实际物理存储位置的对象才有data_object_id,

而一些没有物理属性的object 不存在data_object_id，例如procedure,function,package,data

type,db

link,mv定义,view定义,临时表,分区表定义等等这些object都是没有对应着某个segment，因此它们的data_object_id都为空。大多数情况下两者是相等的。但对object 进行truncate,move, rebuild 等操作后，data_object_id就会发生改变，而object_id不会改变。create table tab1 as select * from emp;SQL> create table tab1 as select * from emp;Table created.create index ind_tab1_pk on tab1(empno);SQL> create index ind_tab1_pk on tab1(empno);Index created.select object_name,object_type,subobject_name,object_id,data_object_id from dba_objects where object_name in ('TAB1','IND_TAB1_PK');SQL> select object_name,object_type,object_id,data_object_id from dba_objects where object_name in ('TAB1','IND_TAB1_PK');OBJECT_NAME object_type OBJECT_ID DATA_OBJECT_ID------------------------------ ----------------------- ------------ ---------------IND_TAB1_PK INDEX 74827 74827TAB1 TABLE 74826 74826SQL> create view v_emp as select * from emp;View created.select object_name,object_type,object_id,data_object_id from dba_objects where object_name='V_EMP';SQL> select object_name,object_type,object_id,data_object_id from dba_objects where object_name='V_EMP';OBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID------------------------------ ------------------- ---------- --------------V_EMP VIEW 74832通过上面查询上面创建的表、索引以及视图可以看到，表和索引这种存在物理存储的是数据库对象是有data_object_id的，而像视图这样的不存在物理存储的数据库对象是没有data_object_id的。

下面对存在data_object_id的数据库对象进测试查看data_object_id的变化与什么有关。

–删除表中的数据查看object_id和data_object_id的变化SQL> delete from tab1 where empno=7788;1 row deleted.SQL> commit;Commit complete.SQL> select object_name,object_type,object_id,data_object_id from dba_objects where object_name in ('TAB1','IND_TAB1_PK');OBJECT_NAME object_type OBJECT_ID DATA_OBJECT_ID------------------------------ ----------------------- ------------ ---------------IND_TAB1_PK INDEX 74827 74827TAB1 TABLE 74826 74826

–对表进行TRUNCATE查看object_id和data_object_id的变化truncate table tab1;SQL> truncate table tab1;Table truncated.SQL> select object_name,object_type,object_id,data_object_id from dba_objects where object_name in ('TAB1','IND_TAB1_PK');OBJECT_NAME object_type OBJECT_ID DATA_OBJECT_ID------------------------------ ---------------------------- ---------- --------------IND_TAB1_PK INDEX 74827 74828TAB1 TABLE 74826 74829

–对表进行MOVE查看object_id和data_object_id的变化alter table tab1 move;SQL> alter table tab1 move;Table altered.SQL> select object_name,object_type,object_id,data_object_id from dba_objects where object_name in ('TAB1','IND_TAB1_PK');OBJECT_NAME object_type OBJECT_ID DATA_OBJECT_ID-------------------------- ------------------------------ ---------- --------------IND_TAB1_PK INDEX 74827 74828TAB1 TABLE 74826 74830

–对索引进行REBUILD查看object_id和data_object_id的变化alter index ind_tab1_pk rebuild;SQL> alter index ind_tab1_pk rebuild;Index altered.SQL> select object_name,object_type,object_id,data_object_id from dba_objects where object_name in ('TAB1','IND_TAB1_PK');OBJECT_NAME object_type OBJECT_ID DATA_OBJECT_ID----------------------------- ---------------------------- ---------- --------------IND_TAB1_PK INDEX 74827 74831TAB1 TABLE 74826 74830

从上面的测试来看当在对表做了TRUNCATE时，表TAB1和表上的索引的data_object_id都发生了变化，在对表做move的时候只有表的data_object_id发生了变化，在对索引重建的时候只有索引的data_object_id发生了变化。在删除表中的数据的时候data_object_id却没有变化。原因是什么呢？–查看视图DBA_OBJECTS的定义SQL> select text from dba_views where view_name='DBA_OBJECTS';TEXT--------------------------------------------------------------------------------select u.name, o.name, o.subname, o.obj#, o.dataobj#,decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',11, 'PACKAGE BODY', 12, 'TRIGGER',13, 'TYPE', 14, 'TYPE BODY',19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',32, 'INDEXTYPE', 33, 'OPERATOR',34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',42, NVL((SELECT 'REWRITE EQUIVALENCE'FROM sum$ sWHERE s.obj#=o.obj#and bitand(s.xpflags, 8388608) = 8388608),'MATERIALIZED VIEW'),43, 'DIMENSION',44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',48, 'CONSUMER GROUP',51, 'SUBSCRIPTION', 52, 'LOCATION',55, 'XML SCHEMA', 56, 'JAVA DATA',57, 'EDITION', 59, 'RULE',60, 'CAPTURE', 61, 'APPLY',62, 'EVALUATION CONTEXT',66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',72, 'SCHEDULER GROUP', 74, 'SCHEDULE', 79, 'CHAIN',81, 'FILE GROUP', 82, 'MINING MODEL', 87, 'ASSEMBLY',90, 'CREDENTIAL', 92, 'CUBE DIMENSION', 93, 'CUBE',94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',100, 'FILE WATCHER', 101, 'DESTINATION','UNDEFINED'),o.ctime, o.mtime,to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),o.namespace,o.defining_editionfrom sys."_CURRENT_EDITION_OBJ" o, sys.user$ uwhere o.owner# = u.user#and o.linkname is nulland (o.type# not in (1 /* INDEX - handled below */,10 /* NON-EXISTENT */)or(o.type# = 1 and 1 = (select 1from sys.ind$ iwhere i.obj# = o.obj#and i.type# in (1, 2, 3, 4, 6, 7, 9))))and o.name != '_NEXT_OBJECT'and o.name != '_default_auditing_options_'and bitand(o.flags, 128) = 0union allselect u.name, l.name, NULL, to_number(null), to_number(null),'DATABASE LINK',l.ctime, to_date(null), NULL, 'VALID','N','N', 'N', NULL, NULLfrom sys.link$ l, sys.user$ uwhere l.owner# = u.user#SQL> desc dba_objectsName Null? Type----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------OWNER VARCHAR2(30)OBJECT_NAME VARCHAR2(128)SUBOBJECT_NAME VARCHAR2(30)OBJECT_ID NUMBERDATA_OBJECT_ID NUMBEROBJECT_TYPE VARCHAR2(19)CREATED DATELAST_DDL_TIME DATETIMESTAMP VARCHAR2(19)STATUS VARCHAR2(7)TEMPORARY VARCHAR2(1)GENERATED VARCHAR2(1)SECONDARY VARCHAR2(1)NAMESPACE NUMBEREDITION_NAME VARCHAR2(30)

data_object_id在dba_objects视图中的顺序可以对应到视图定义中的o.dataobj#字段，该字段来自于视图_CURRENT_EDITION_OBJ

接着查看该视图的定义SQL> select text from dba_views where view_name='_CURRENT_EDITION_OBJ';TEXT--------------------------------------------------------------------------------select o."OBJ#",o."DATAOBJ#",o."OWNER#",o."NAME",o."NAMESPACE",o."SUBNAME",o."TYPE#",o."CTIME",o."MTIME",o."STIME",o."STATUS",o."REMOTEOWNER",o."LINKNAME",o."FLAGS",o."OID$",o."SPARE1",o."SPARE2",o."SPARE3",o."SPARE4",o."SPARE5",o."SPARE6",o.spare3,case when (o.type# not in (4,5,7,8,9,10,11,12,13,14,22,87) orbitand(u.spare1, 16) = 0) thennullwhen (u.type# = 2) then(select eo.name from obj$ eo where eo.obj# = u.spare2)else'ORA$BASE'endfrom obj$ o, user$ uwhere o.owner# = u.user#and ( /* non-versionable object */( o.type# not in (4,5,7,8,9,10,11,12,13,14,22,87,88)or bitand(u.spare1, 16) = 0)/* versionable object visible in current edition */or ( o.type# in (4,5,7,8,9,10,11,12,13,14,22,87)and ( (u.type# <> 2 andsys_context('userenv', 'current_edition_name') = 'ORA$BASE')or (u.type# = 2 andu.spare2 = sys_context('userenv', 'current_edition_id'))or exists (select 1 from obj$ o2, user$ u2where o2.type# = 88and o2.dataobj# = o.obj#and o2.owner# = u2.user#and u2.type# = 2and u2.spare2 =sys_context('userenv', 'current_edition_id')))))从该视图的定义中可以看到o.DATAOBJ#字段来自于内部表obj，而obj.dataobj#又对应着seg$.HWMINCRselect max(HWMINCR) from sys.seg$;SQL> select max(HWMINCR) from sys.seg$;MAX(HWMINCR)------------74831这里的HWMINCR就是data_object_id的来源，每次seg里生成新的一条记录都会增加HWMINCR这个值，同时obj.dataobj#也会跟着变化。从前面可以看到74831是tab1表上的索引ind_tab1_pk的data_object_id，那么现在对该索引进行rebuild再查看seg$.HWMINCR最大值的变化alter index ind_tab1_pk rebuild;SQL> alter index ind_tab1_pk rebuild;Index altered.SQL> select max(HWMINCR) from sys.seg$;MAX(HWMINCR)------------74833

这时候发现max(HWMINCR)变为了74833，这里的HWMINCR就是data_object_id的来源，每当数据库上存在物理段的对象段发生变化的时候都会在seg里生成新的一条记录增加HWMINCR这个值，同时obj.dataobj#也会跟着变化。最后在视图dba_objects中对应对象的data_object_id值也会发生变化。

墨天轮原文链接：https://www.modb.pro/db/24365(复制到浏览器中打开或者点击“阅读原文”)