[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#非空的记录很少的原因,这样前面的修复索引成为可能,相对容易的缘故.
上一篇： [笔记][SQL] 连接join
下一篇： 利用pandas读取中文数据集的方法