[20190227]简单探究tab$的bojb#字段.txt

来源：这里教程网时间：2026-03-03 12:58:25 作者：

[20190227]简单探究tab$的bojb#字段.txt --//上午做了删除tab$表,其对应索引i_tab1的恢复,我一直以为这个索引会很大,没有想到在我的测试环境仅仅139个键值. --//查看/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/dcore.sql的内容tab$的定义如下.(我使用版本11.2.0.4) 1.环境: SYS@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production create table tab$ /* table table */ ( obj# number not null, /* object number */ /* DO NOT CREATE INDEX ON DATAOBJ# AS IT WILL BE UPDATED IN A SPACE * TRANSACTION DURING TRUNCATE */ dataobj# number, /* data layer object number */ ts# number not null, /* tablespace number */ file# number not null, /* segment header file number */ block# number not null, /* segment header block number */ bobj# number, /* base object number (cluster / iot) */ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ... spare1 number, /* used to store hakan_kqldtvc */ spare2 number, /* committed partition # used by drop column */ spare3 number, /* summary sequence number */ spare4 varchar2(1000), /* committed RID used by drop column */ spare5 varchar2(1000), /* summary related information on table */ spare6 date /* flashback timestamp */ ) cluster c_obj#(obj#) / --//可以从后面的注解发现base object number (cluster / iot).也就是IOT表以及cluster table的base object number. 2.对于cluster table: select obj# from sys.tab$ where bobj#=2 minus select object_id from dba_objects where data_object_id=2; no rows selected select object_id from dba_objects where data_object_id=2 minus select obj# from sys.tab$ where bobj#=2 OBJECT_ID ---------- 2 SYS@book> select * from sys.tab$ where obj#=2 ; no rows selected SCOTT@book> select obj#,dataobj#,bobj#,tab# from sys.tab$ a where bobj#=2 order by obj#; OBJ# DATAOBJ# BOBJ# TAB# ---------- ---------- ---------- ---------- 4 2 2 1 5 2 2 2 19 2 2 3 20 2 2 4 21 2 2 5 80 2 2 6 83 2 2 7 86 2 2 8 88 2 2 9 92 2 2 10 95 2 2 11 114 2 2 12 174 2 2 13 252 2 2 14 253 2 2 15 512 2 2 16 517 2 2 17 17 rows selected. --//可以发现tab$表中没有obj#=2的记录.也就是对于cluster table,tab$的bobj#字段仅仅记录各个子表的情况.并且等于dataobj#. 3.IOT表的情况呢? --//建立IOT看看: SCOTT@book> create table t_iot ( a varchar2(10),b varchar2(10),vc varchar2(1200), constraint t_iot_pk PRIMARY KEY(a)) ORGANIZATION INDEX; Table created. SCOTT@book> select object_id,data_object_id,object_name from dba_objects where object_name in ('T_IOT_PK','T_IOT') and owner=user; OBJECT_ID DATA_OBJECT_ID OBJECT_NAME ---------- -------------- -------------------- 91110 T_IOT 91111 91111 T_IOT_PK --//对于IOT表的本质实际上是一个索引,仅仅索引段有空间分配(DATA_OBJECT_ID非空). SCOTT@book> select obj#,dataobj#,bobj# from sys.tab$ a where obj# in (91110,91111); OBJ# DATAOBJ# BOBJ# ---------- ---------- ---------- 91110 --//表IOT没有任何段分配.奇怪没有obj#=91111的段.没有插入记录吗? SCOTT@book> insert into t_iot values ('1','a','a'); 1 row created. SCOTT@book> commit ; Commit complete. SCOTT@book> select obj#,dataobj#,bobj# from sys.tab$ a where obj# in (91110,91111); OBJ# DATAOBJ# BOBJ# ---------- ---------- ---------- 91110 --//当然也很好理解OBJECT_ID=91111是索引段,不会出现在表tab$里面.如何理解注解base object number (cluster / iot)呢? --//也就是索引组织表(IOT)什么时候会出现表段呢?难道是OVERFLOW段吗?建立包含overflow段的iot表看看: SCOTT@book> create table z_iot ( a varchar2(10),b varchar2(10),vc varchar2(1200), constraint z_iot_pk PRIMARY KEY(a)) ORGANIZATION INDEX OVERFLOW TABLESPACE users; Table created. SCOTT@book> select object_id,data_object_id,object_name from dba_objects where object_name in ('Z_IOT_PK','Z_IOT') and owner=user; OBJECT_ID DATA_OBJECT_ID OBJECT_NAME ---------- -------------- -------------------- 91112 Z_IOT 91114 91114 Z_IOT_PK --//注意1个细节,object_id出现跳号. SCOTT@book> select * from dba_objects where object_id between 91112 and 91114; OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME ------ -------------------- ---------- ---------- -------------- ----------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------ SCOTT Z_IOT_PK 91114 91114 INDEX 2019-02-28 15:51:44 2019-02-28 15:51:44 2019-02-28:15:51:44 VALID N N N 4 SCOTT SYS_IOT_OVER_91112 91113 91113 TABLE 2019-02-28 15:51:44 2019-02-28 15:51:44 2019-02-28:15:51:44 VALID N Y N 1 SCOTT Z_IOT 91112 TABLE 2019-02-28 15:51:44 2019-02-28 15:51:44 2019-02-28:15:51:44 VALID N N N 1 --//可以看出多了一个对象SYS_IOT_OVER_91112就是OVERFLOW段(类型是table),也就是这个溢出段按照表的形式保持信息,对于这些东西 --//很少探究.正常的业务表很少使用IOT,至少国内的情况如此. SCOTT@book> select obj#,dataobj#,bobj# from sys.tab$ a where obj# between 91112 and 91114; OBJ# DATAOBJ# BOBJ# ---------- ---------- ---------- 91112 91113 91113 91113 91112 --//注意看它们之间的关系. --//OBJ#=91112,对应是表Z_IOT,没有数据段分配,DATAOBJ#等于null,BOBJ#=91113,对应是SYS_IOT_OVER_91112(后面的数字与Z_IOT的object_id一致) --//OBJ#=91113,对应的是SYS_IOT_OVER_91112.DATAOBJ#=91113.BOBJ#=91112,对应的是Z_IOT,这也就是注解讲base object number(cluster / iot). --//有点绕,大家慢慢理解吧... --//这也就是sys.tab$表为什么bobj#非空的记录很少的原因,这样前面的修复索引成为可能,相对容易的缘故.