[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#非空的記錄很少的原因,這樣前面的修復索引成為可能,相對容易的緣故.

[20190227]簡單探究tab$的bojb#欄位.txt

相關文章