關於資料庫open的深入探究(轉)

關於資料庫open的深入探究(轉)[@more@]

關於資料庫open的深入探究

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 131142648 bytes

Fixed Size 451576 bytes

Variable Size 104857600 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

Database mounted.

SQL> alter session set sql_trace = true;

Session altered.

SQL> alter database open;

Database altered.

SQL>

首先我們來參考跟蹤檔案的前部分(參考附件)

這是第一個物件的建立

create table bootstrap$ ( line# number not null, obj#

number not null, sql_text varchar2(4000) not null) storage (initial

50K objno 56 extents (file 1 block 377))

接下來我們來看執行的是

select line#, sql_text

from

bootstrap$ where obj# != :1

(這裡實際上是逐步提取內容建立字典表本身的結構,根據後面內容可知)

那麼在這裡我們在資料庫中來看看 bootstap$

SQL> desc bootstrap$

Name Null? Type

----------------------------------------- -------- ----------------------------

LINE# NOT NULL NUMBER

OBJ# NOT NULL NUMBER

SQL_TEXT NOT NULL VARCHAR2(4000)

SQL>

SQL> select count(*) from bootstrap$;

57

SQL>

SQL> select obj#,sql_text from bootstrap$ where rownum<11;

-1

8.0.0.0.0

0

CREATE ROLLBACK SEGMENT SYSTEM STORAGE ( INITIAL 112K NEXT 1024K MINEXTENTS 1 M

AXEXTENTS 32765 OBJNO 0 EXTENTS (FILE 1 BLOCK 9))

8

CREATE CLUSTER C_FILE#_BLOCK#("TS#" NUMBER,"SEGFILE#" NUMBER,"SEGBLOCK#" NUMBER)

PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 24K NEXT 1024K

MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 8 EXTENTS (FILE 1 BLOCK

73)) SIZE 225

9

CREATE INDEX I_FILE#_BLOCK# ON CLUSTER C_FILE#_BLOCK# PCTFREE 10 INITRANS 2 MAXT

RANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PC

TINCREASE 0 OBJNO 9 EXTENTS (FILE 1 BLOCK 81))

14

CREATE TABLE SEG$("FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"TYPE#" NUMBE

R NOT NULL,"TS#" NUMBER NOT NULL,"BLOCKS" NUMBER NOT NULL,"EXTENTS" NUMBER NOT N

ULL,"INIEXTS" NUMBER NOT NULL,"MINEXTS" NUMBER NOT NULL,"MAXEXTS" NUMBER NOT NUL

L,"EXTSIZE" NUMBER NOT NULL,"EXTPCT" NUMBER NOT NULL,"USER#" NUMBER NOT NULL,"LI

STS" NUMBER,"GROUPS" NUMBER,"BITMAPRANGES" NUMBER NOT NULL,"CACHEHINT" NUMBER NO

T NULL,"SCANHINT" NUMBER NOT NULL,"HWMINCR" NUMBER NOT NULL,"SPARE1" NUMBER,"SPA

RE2" NUMBER) STORAGE ( OBJNO 14 TABNO 2) CLUSTER C_FILE#_BLOCK#(TS#,FILE#,BLOCK

#)

5

CREATE TABLE CLU$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"TS#" NUMBER NOT NULL

,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"COLS" NUMBER NOT NULL,"PCTFRE

E$" NUMBER NOT NULL,"PCTUSED$" NUMBER NOT NULL,"INITRANS" NUMBER NOT NULL,"MAXTR

ANS" NUMBER NOT NULL,"SIZE$" NUMBER,"HASHFUNC" VARCHAR2(30),"HASHKEYS" NUMBER,"F

UNC" NUMBER,"EXTIND" NUMBER,"FLAGS" NUMBER,"DEGREE" NUMBER,"INSTANCES" NUMBER,"A

VGCHN" NUMBER,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" NUMBER,"S

PARE5" VARCHAR2(1000),"SPARE6" VARCHAR2(1000),"SPARE7" DATE) STORAGE ( OBJNO 5

TABNO 2) CLUSTER C_OBJ#(OBJ#)

6

CREATE CLUSTER C_TS#("TS#" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255

STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREAS

E 0 OBJNO 6 EXTENTS (FILE 1 BLOCK 57))

7

CREATE INDEX I_TS# ON CLUSTER C_TS# PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (

INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO

7 EXTENTS (FILE 1 BLOCK 65))

18

CREATE TABLE OBJ$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"OWNER#" NUMBER NOT N

ULL,"NAME" VARCHAR2(30) NOT NULL,"NAMESPACE" NUMBER NOT NULL,"SUBNAME" VARCHAR2(

30),"TYPE#" NUMBER NOT NULL,"CTIME" DATE NOT NULL,"MTIME" DATE NOT NULL,"STIME"

DATE NOT NULL,"STATUS" NUMBER NOT NULL,"REMOTEOWNER" VARCHAR2(30),"LINKNAME" VAR

CHAR2(128),"FLAGS" NUMBER,"OID$" RAW(16),"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3

" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) PCTFREE

10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 16K NEXT 1024K MINEXTEN

TS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 18 EXTENTS (FILE 1 BLOCK 121))

36

CREATE UNIQUE INDEX I_OBJ1 ON OBJ$(OBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STOR

AGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 O

BJNO 36 EXTENTS (FILE 1 BLOCK 217))

10 rows selected.

SQL>

由這裡我們可以看出，bootstrap$ 中實際上是記錄了一些資料庫系統基本物件的建立語句

那對於我們資料庫來說，也可以看做建立一個表結構，透過這個結構可以透過關係型資料庫的方式去獲取檔案中資料

ok,接下來我們看看trace檔案中的內容，發現

CREATE ROLLBACK SEGMENT SYSTEM STORAGE ( INITIAL 112K NEXT 1024K MINEXTENTS

1 MAXEXTENTS 32765 OBJNO 0 EXTENTS (FILE 1 BLOCK 9))

在資料庫系統表空間的頭部建立了系統回滾段（block 9 開始，1---8 屬於資料檔案頭）

再往下

CREATE CLUSTER C_OBJ#("OBJ#" NUMBER) PCTFREE 5 PCTUSED 40 INITRANS 2 MAXTRANS

255 STORAGE ( INITIAL 136K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 OBJNO 2 EXTENTS (FILE 1 BLOCK 25)) SIZE 800

這裡同樣是直接指定了該cluster 的段頭位置 block 25

SQL> select file_id,block_id from dba_extents where segment_name = 'C_OBJ#';

1 25

1 33

1 41

1 3241

1 4441

1 4473

1 4489

1 4513

1 4529

1 4561

1 4585

1 4609

1 4641

1 4681

1 4753

1 4833

1 4873

1 12681

1 23689

1 26249

20 rows selected.

CREATE INDEX I_OBJ# ON CLUSTER C_OBJ# PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 OBJNO 3 EXTENTS (FILE 1 BLOCK 49))

CREATE TABLE TAB$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"TS#" NUMBER NOT

NULL,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"BOBJ#" NUMBER,"TAB#"

NUMBER,"COLS" NUMBER NOT NULL,"CLUCOLS" NUMBER,"PCTFREE$" NUMBER NOT NULL,

"PCTUSED$" NUMBER NOT NULL,"INITRANS" NUMBER NOT NULL,"MAXTRANS" NUMBER NOT

NULL,"FLAGS" NUMBER NOT NULL,"AUDIT$" VARCHAR2(38) NOT NULL,"ROWCNT" NUMBER,

"BLKCNT" NUMBER,"EMPCNT" NUMBER,"AVGSPC" NUMBER,"CHNCNT" NUMBER,"AVGRLN"

NUMBER,"AVGSPC_FLB" NUMBER,"FLBCNT" NUMBER,"ANALYZETIME" DATE,"SAMPLESIZE"

NUMBER,"DEGREE" NUMBER,"INSTANCES" NUMBER,"INTCOLS" NUMBER NOT NULL,

"KERNELCOLS" NUMBER NOT NULL,"PROPERTY" NUMBER NOT NULL,"TRIGFLAG" NUMBER,

"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),

"SPARE5" VARCHAR2(1000),"SPARE6" DATE) STORAGE ( OBJNO 4 TABNO 1) CLUSTER

C_OBJ#(OBJ#)

我們可以看到，從tab$ 開始，儲存發生了變化，

STORAGE ( OBJNO 4 TABNO 1) CLUSTER C_OBJ#(OBJ#)

那這裡的 objno 4 tabno 1 表示什麼意思呢?(透過 cluster C_OBJ#(OBJ#)我們就可以找到表tab$自身)

SQL> select * from tab$ where obj# = 4 and tab# = 1;

OBJ# DATAOBJ# TS# FILE# BLOCK# BOBJ# TAB#

---------- ---------- ---------- ---------- ---------- ---------- ----------

COLS CLUCOLS PCTFREE$ PCTUSED$ INITRANS MAXTRANS FLAGS

---------- ---------- ---------- ---------- ---------- ---------- ----------

AUDIT$ ROWCNT BLKCNT EMPCNT

-------------------------------------- ---------- ---------- ----------

AVGSPC CHNCNT AVGRLN AVGSPC_FLB FLBCNT ANALYZETI SAMPLESIZE

---------- ---------- ---------- ---------- ---------- --------- ----------

DEGREE INSTANCES INTCOLS KERNELCOLS PROPERTY TRIGFLAG SPARE1

---------- ---------- ---------- ---------- ---------- ---------- ----------

SPARE2 SPARE3

---------- ----------

SPARE4

--------------------------------------------------------------------------------

SPARE5

--------------------------------------------------------------------------------

SPARE6

---------

4 2 0 1 25 2 1

37 1 0 0 0 0 17

-------------------------------- 921 428 83

2381 0 139 6368 7 11-AUG-03 921

37 37 1024 0 0

06-AUG-03

SQL>

由這裡可以看出，儲存引數從這裡開始，就從資料檔案中固定表的記錄中取出來

由此再往trace檔案看下去，就會發現這個時候資料庫已經可以透過已有資訊從系統表空間檔案中提取結構資訊和資料資訊

我們看上面關於儲存的定義

CREATE CLUSTER C_OBJ#("OBJ#" NUMBER) PCTFREE 5 PCTUSED 40 INITRANS 2 MAXTRANS

255 STORAGE ( INITIAL 136K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 OBJNO 2 EXTENTS (FILE 1 BLOCK 25)) SIZE 800

這裡的extents 指明瞭是 block 25,我們假設在初始化引數檔案中db_block_size設定不當，則必然導致檔案位置定位錯誤

之所以要來研究這個問題（以前研究了今天寫出來而已），是因為最早思考這樣一個問題

我們要查詢自己系統的一個表，則首先是去資料字典中找到該表的結構性資訊

這些結構性資訊儲存在資料字典表中，但我們又從哪裡獲得資料字典表本身的結構性資訊呢？

雖然從表中我們可以查詢到字典表本身的結構性資訊，那在資料庫open的時候最初是如何確定結構的？

是寫死在程式中呢？還是怎樣處理的

由上面內容我們可以看出，程式中只要提供 bootstrap$ 的建立指令碼，確定好bootstap$的結構同時指定了段頭的位置

然後就能透過段頭去獲取bootstrap$中的內容，而這些內容就是為一些字典表建立結構的sql_text

這樣就因了bootstrap$這個天生的蛋，孵化出了c_obj#,tab$等等雞，這些雞又繼續生蛋，則完成了資料庫字典表結構的建立

也就是說，oracle用關係型表來實現了自身結構的建