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

[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#非空的记录很少的原因,这样前面的修复索引成为可能,相对容易的缘故.

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐