[20160228]關於sys.bootstrap$.txt
[20160228]關於sys.bootstrap$.txt
--關於sys.bootstrap$,在啟動oracle資料庫中佔用很重要的位置,我以前看eygle,biti的blog,自已以前也探究過,
--有許多東西一直不理解,自己也重複探究看看:
1.環境:
SCOTT@book> @ &r/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
SCOTT@book> select rowid,a.* from SYS.BOOTSTRAP$ a order by line#;
ROWID LINE# OBJ# SQL_TEXT
------------------ ---------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AAAAA7AABAAAAIJAAA -1 -1 8.0.0.0.0
AAAAA7AABAAAAIJAAB 0 0 CREATE ROLLBACK SEGMENT SYSTEM STORAGE ( INITIAL 112K NEXT 56K MINEXTENTS 1 MAXEXTENTS 32765 OBJNO 0 EXTENTS (FILE 1 BLOCK 128))
AAAAA7AABAAAAILAAG 2 2 CREATE CLUSTER C_OBJ#("OBJ#" NUMBER) PCTFREE 5 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 136K NEXT 200K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 2 EXTENTS (FILE 1 BLOCK 144))
SIZE 800
AAAAA7AABAAAAILAAH 3 3 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 168))
AAAAA7AABAAAAILAAI 4 4 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,"EMPCN
T" NUMBER,"AVGSPC" NUMBER,"CHNCNT" NUMBER,"AVGRLN" NUMBER,"AVGSPC_FLB" NUMBER,"FLBCNT" NUMBER,"ANALYZETIME" DATE,"SAMPLESIZE" NUMBER,"DEGREE" NUMBER,"INSTANCES" NUMBER,"INTCOLS" NUMBER NOT NULL,"KERNE
LCOLS" 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 TA
BNO 1) CLUSTER C_OBJ#(OBJ#)
AAAAA7AABAAAAIKAAE 5 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,"PCTFREE$" NUMBER NOT NULL,"PCTUSED$" NUMBER NO
T NULL,"INITRANS" NUMBER NOT NULL,"MAXTRANS" NUMBER NOT NULL,"SIZE$" NUMBER,"HASHFUNC" VARCHAR2(30),"HASHKEYS" NUMBER,"FUNC" NUMBER,"EXTIND" NUMBER,"FLAGS" NUMBER,"DEGREE" NUMBER,"INSTANCES" NUMBER,"A
VGCHN" NUMBER,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" NUMBER,"SPARE5" VARCHAR2(1000),"SPARE6" VARCHAR2(1000),"SPARE7" DATE) STORAGE ( OBJNO 5 TABNO 2) CLUSTER C_OBJ#(OBJ#)
AAAAA7AABAAAAIJAAW 6 6 CREATE CLUSTER C_TS#("TS#" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 6 EXTENTS (FILE 1 BLOCK 176))
AAAAA7AABAAAAIJAAX 7 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 184))
AAAAA7AABAAAAIKAAJ 8 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 PCTI
NCREASE 0 OBJNO 8 EXTENTS (FILE 1 BLOCK 192)) SIZE 225
AAAAA7AABAAAAIKAAK 9 9 CREATE INDEX I_FILE#_BLOCK# ON CLUSTER C_FILE#_BLOCK# PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 9 EXTENTS (FILE 1 BLOC
K 200))
AAAAA7AABAAAAIKAAL 10 10 CREATE CLUSTER C_USER#("USER#" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 10 EXTENTS (FILE 1 BLOCK 2
08)) SIZE 372
AAAAA7AABAAAAIKAAM 11 11 CREATE INDEX I_USER# ON CLUSTER C_USER# PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 11 EXTENTS (FILE 1 BLOCK 216))
AAAAA7AABAAAAIKAAO 12 12 CREATE TABLE FET$("TS#" NUMBER NOT NULL,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"LENGTH" NUMBER NOT NULL) STORAGE ( OBJNO 12 TABNO 1) CLUSTER C_TS#(TS#)
AAAAA7AABAAAAIJAAS 13 13 CREATE TABLE UET$("SEGFILE#" NUMBER NOT NULL,"SEGBLOCK#" NUMBER NOT NULL,"EXT#" NUMBER NOT NULL,"TS#" NUMBER NOT NULL,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"LENGTH" NUMBER NOT NULL) STORAGE
( OBJNO 13 TABNO 1) CLUSTER C_FILE#_BLOCK#(TS#,SEGFILE#,SEGBLOCK#)
AAAAA7AABAAAAIJAAV 14 14 CREATE TABLE SEG$("FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"TYPE#" NUMBER NOT NULL,"TS#" NUMBER NOT NULL,"BLOCKS" NUMBER NOT NULL,"EXTENTS" NUMBER NOT NULL,"INIEXTS" NUMBER NOT NULL,"MINEXTS"
NUMBER NOT NULL,"MAXEXTS" NUMBER NOT NULL,"EXTSIZE" NUMBER NOT NULL,"EXTPCT" NUMBER NOT NULL,"USER#" NUMBER NOT NULL,"LISTS" NUMBER,"GROUPS" NUMBER,"BITMAPRANGES" NUMBER NOT NULL,"CACHEHINT" NUMBER NO
T NULL,"SCANHINT" NUMBER NOT NULL,"HWMINCR" NUMBER NOT NULL,"SPARE1" NUMBER,"SPARE2" NUMBER) STORAGE ( OBJNO 14 TABNO 2) CLUSTER C_FILE#_BLOCK#(TS#,FILE#,BLOCK#)
AAAAA7AABAAAAIJAAH 15 15 CREATE TABLE UNDO$("US#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"USER#" NUMBER NOT NULL,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"SCNBAS" NUMBER,"SCNWRP" NUMBER,"XACTSQN" NUMBER,"UNDOSQN
" NUMBER,"INST#" NUMBER,"STATUS$" NUMBER NOT NULL,"TS#" NUMBER,"UGRP#" NUMBER,"KEEP" NUMBER,"OPTIMAL" NUMBER,"FLAGS" NUMBER,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPA
RE5" VARCHAR2(1000),"SPARE6" DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 15 EXTENTS (FILE 1 BLOCK 224))
AAAAA7AABAAAAIKAAH 16 16 CREATE TABLE TS$("TS#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"OWNER#" NUMBER NOT NULL,"ONLINE$" NUMBER NOT NULL,"CONTENTS$" NUMBER NOT NULL,"UNDOFILE#" NUMBER,"UNDOBLOCK#" NUMBER,"BLOCKSIZE" NU
MBER NOT NULL,"INC#" NUMBER NOT NULL,"SCNWRP" NUMBER,"SCNBAS" NUMBER,"DFLMINEXT" NUMBER NOT NULL,"DFLMAXEXT" NUMBER NOT NULL,"DFLINIT" NUMBER NOT NULL,"DFLINCR" NUMBER NOT NULL,"DFLMINLEN" NUMBER NOT
NULL,"DFLEXTPCT" NUMBER NOT NULL,"DFLOGGING" NUMBER NOT NULL,"AFFSTRENGTH" NUMBER NOT NULL,"BITMAPPED" NUMBER NOT NULL,"PLUGGED" NUMBER NOT NULL,"DIRECTALLOWED" NUMBER NOT NULL,"FLAGS" NUMBER NOT NULL
,"PITRSCNWRP" NUMBER,"PITRSCNBAS" NUMBER,"OWNERINSTANCE" VARCHAR2(30),"BACKUPOWNER" VARCHAR2(30),"GROUPNAME" VARCHAR2(30),"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" VARCHAR2(1000),"SPARE4" DATE) STORAGE
( OBJNO 16 TABNO 2) CLUSTER C_TS#(TS#)
AAAAA7AABAAAAIJAAP 17 17 CREATE TABLE FILE$("FILE#" NUMBER NOT NULL,"STATUS$" NUMBER NOT NULL,"BLOCKS" NUMBER NOT NULL,"TS#" NUMBER,"RELFILE#" NUMBER,"MAXEXTEND" NUMBER,"INC" NUMBER,"CRSCNWRP" NUMBER,"CRSCNBAS" NUMBER,"OWNERI
NSTANCE" VARCHAR2(30),"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" VARCHAR2(1000),"SPARE4" DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 214
7483645 PCTINCREASE 0 OBJNO 17 EXTENTS (FILE 1 BLOCK 232))
AAAAA7AABAAAAILAAA 18 18 CREATE TABLE OBJ$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"OWNER#" NUMBER NOT NULL,"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" VARCHAR2(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 104K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
OBJNO 18 EXTENTS (FILE 1 BLOCK 240))
AAAAA7AABAAAAIJAAT 19 19 CREATE TABLE IND$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"TS#" NUMBER NOT NULL,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"BO#" NUMBER NOT NULL,"INDMETHOD#" NUMBER NOT NULL,"COLS" NUMBER NOT N
ULL,"PCTFREE$" NUMBER NOT NULL,"INITRANS" NUMBER NOT NULL,"MAXTRANS" NUMBER NOT NULL,"PCTTHRES$" NUMBER,"TYPE#" NUMBER NOT NULL,"FLAGS" NUMBER NOT NULL,"PROPERTY" NUMBER NOT NULL,"BLEVEL" NUMBER,"LEAF
CNT" NUMBER,"DISTKEY" NUMBER,"LBLKKEY" NUMBER,"DBLKKEY" NUMBER,"CLUFAC" NUMBER,"ANALYZETIME" DATE,"SAMPLESIZE" NUMBER,"ROWCNT" NUMBER,"INTCOLS" NUMBER NOT NULL,"DEGREE" NUMBER,"INSTANCES" NUMBER,"TRUN
CCNT" NUMBER,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) STORAGE ( OBJNO 19 TABNO 3) CLUSTER C_OBJ#(BO#)
AAAAA7AABAAAAIJAAC 20 20 CREATE TABLE ICOL$("OBJ#" NUMBER NOT NULL,"BO#" NUMBER NOT NULL,"COL#" NUMBER NOT NULL,"POS#" NUMBER NOT NULL,"SEGCOL#" NUMBER NOT NULL,"SEGCOLLENGTH" NUMBER NOT NULL,"OFFSET" NUMBER NOT NULL,"INTCOL#
" NUMBER NOT NULL,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) STORAGE ( OBJNO 20 TABNO 4) CLUSTER C_OBJ#(BO#)
AAAAA7AABAAAAIKAAA 21 21 CREATE TABLE COL$("OBJ#" NUMBER NOT NULL,"COL#" NUMBER NOT NULL,"SEGCOL#" NUMBER NOT NULL,"SEGCOLLENGTH" NUMBER NOT NULL,"OFFSET" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"TYPE#" NUMBER NOT NULL,"
LENGTH" NUMBER NOT NULL,"FIXEDSTORAGE" NUMBER NOT NULL,"PRECISION#" NUMBER,"SCALE" NUMBER,"NULL$" NUMBER NOT NULL,"DEFLENGTH" NUMBER,"DEFAULT$" LONG,"INTCOL#" NUMBER NOT NULL,"PROPERTY" NUMBER NOT NUL
L,"CHARSETID" NUMBER,"CHARSETFORM" NUMBER,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) STORAGE ( OBJNO 21 TABNO 5) CLUSTER C_OBJ#(OBJ
#)
AAAAA7AABAAAAIKAAS 22 22 CREATE TABLE USER$("USER#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"TYPE#" NUMBER NOT NULL,"PASSWORD" VARCHAR2(30),"DATATS#" NUMBER NOT NULL,"TEMPTS#" NUMBER NOT NULL,"CTIME" DATE NOT NULL,"PTIME
" DATE,"EXPTIME" DATE,"LTIME" DATE,"RESOURCE$" NUMBER NOT NULL,"AUDIT$" VARCHAR2(38),"DEFROLE" NUMBER NOT NULL,"DEFGRP#" NUMBER,"DEFGRP_SEQ#" NUMBER,"ASTATUS" NUMBER NOT NULL,"LCOUNT" NUMBER NOT NULL,
"DEFSCHCLASS" VARCHAR2(30),"EXT_USERNAME" VARCHAR2(4000),"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) STORAGE ( OBJNO 22 TABNO 1) CLU
STER C_USER#(USER#)
AAAAA7AABAAAAIKAAF 23 23 CREATE TABLE PROXY_DATA$("CLIENT#" NUMBER NOT NULL,"PROXY#" NUMBER NOT NULL,"CREDENTIAL_TYPE#" NUMBER NOT NULL,"CREDENTIAL_VERSION#" NUMBER NOT NULL,"CREDENTIAL_MINOR#" NUMBER NOT NULL,"FLAGS" NUMBER
NOT NULL) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 23 EXTENTS (FILE 1 BLOCK 248))
AAAAA7AABAAAAIKAAG 24 24 CREATE UNIQUE INDEX I_PROXY_DATA$ ON PROXY_DATA$(CLIENT#,PROXY#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 24 EXTENTS
(FILE 1 BLOCK 256))
AAAAA7AABAAAAIJAAM 25 25 CREATE TABLE PROXY_ROLE_DATA$("CLIENT#" NUMBER NOT NULL,"PROXY#" NUMBER NOT NULL,"ROLE#" NUMBER NOT NULL) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 M
AXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 25 EXTENTS (FILE 1 BLOCK 264))
AAAAA7AABAAAAIJAAN 26 26 CREATE INDEX I_PROXY_ROLE_DATA$_1 ON PROXY_ROLE_DATA$(CLIENT#,PROXY#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 26 EXT
ENTS (FILE 1 BLOCK 272))
AAAAA7AABAAAAIJAAO 27 27 CREATE UNIQUE INDEX I_PROXY_ROLE_DATA$_2 ON PROXY_ROLE_DATA$(CLIENT#,PROXY#,ROLE#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
OBJNO 27 EXTENTS (FILE 1 BLOCK 280))
AAAAA7AABAAAAIJAAE 28 28 CREATE TABLE CON$("OWNER#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"CON#" NUMBER NOT NULL,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6"
DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 28 EXTENTS (FILE 1 BLOCK 288))
AAAAA7AABAAAAIJAAK 29 29 CREATE CLUSTER C_COBJ#("OBJ#" NUMBER) PCTFREE 0 PCTUSED 50 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 56K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 29 EXTENTS (FILE 1 BLOCK 296
)) SIZE 300
AAAAA7AABAAAAIJAAL 30 30 CREATE INDEX I_COBJ# ON CLUSTER C_COBJ# PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 30 EXTENTS (FILE 1 BLOCK 304))
AAAAA7AABAAAAILAAK 31 31 CREATE TABLE CDEF$("CON#" NUMBER NOT NULL,"OBJ#" NUMBER NOT NULL,"COLS" NUMBER,"TYPE#" NUMBER NOT NULL,"ROBJ#" NUMBER,"RCON#" NUMBER,"RRULES" VARCHAR2(3),"MATCH#" NUMBER,"REFACT" NUMBER,"ENABLED" NUMB
ER,"CONDLENGTH" NUMBER,"CONDITION" LONG,"INTCOLS" NUMBER,"MTIME" DATE,"DEFER" NUMBER,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) STOR
AGE ( OBJNO 31 TABNO 1) CLUSTER C_COBJ#(OBJ#)
AAAAA7AABAAAAIKAAP 32 32 CREATE TABLE CCOL$("CON#" NUMBER NOT NULL,"OBJ#" NUMBER NOT NULL,"COL#" NUMBER NOT NULL,"POS#" NUMBER,"INTCOL#" NUMBER NOT NULL,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),
"SPARE5" VARCHAR2(1000),"SPARE6" DATE) STORAGE ( OBJNO 32 TABNO 2) CLUSTER C_COBJ#(OBJ#)
AAAAA7AABAAAAILAAJ 33 33 CREATE INDEX I_TAB1 ON TAB$(BOBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 33 EXTENTS (FILE 1 BLOCK 312))
AAAAA7AABAAAAIJAAI 34 34 CREATE UNIQUE INDEX I_UNDO1 ON UNDO$(US#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 34 EXTENTS (FILE 1 BLOCK 320))
AAAAA7AABAAAAIJAAJ 35 35 CREATE INDEX I_UNDO2 ON UNDO$(NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 35 EXTENTS (FILE 1 BLOCK 328))
AAAAA7AABAAAAILAAB 36 36 CREATE UNIQUE INDEX I_OBJ1 ON OBJ$(OBJ#,OWNER#,TYPE#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 36 EXTENTS (FILE 1 BLO
CK 336))
AAAAA7AABAAAAILAAC 37 37 CREATE UNIQUE INDEX I_OBJ2 ON OBJ$(OWNER#,NAME,NAMESPACE,REMOTEOWNER,LINKNAME,SUBNAME,TYPE#,SPARE3,OBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 16K NEXT 104K MINEXTENTS 1 MAXEXTENTS 214
7483645 PCTINCREASE 0 OBJNO 37 EXTENTS (FILE 1 BLOCK 344))
AAAAA7AABAAAAILAAD 38 38 CREATE INDEX I_OBJ3 ON OBJ$(OID$) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 38 EXTENTS (FILE 1 BLOCK 352))
AAAAA7AABAAAAILAAE 39 39 CREATE INDEX I_OBJ4 ON OBJ$(DATAOBJ#,TYPE#,OWNER#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 39 EXTENTS (FILE 1 BLOCK
360))
AAAAA7AABAAAAILAAF 40 40 CREATE UNIQUE INDEX I_OBJ5 ON OBJ$(SPARE3,NAME,NAMESPACE,TYPE#,OWNER#,REMOTEOWNER,LINKNAME,SUBNAME,OBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 21
47483645 PCTINCREASE 0 OBJNO 40 EXTENTS (FILE 1 BLOCK 368))
AAAAA7AABAAAAIJAAU 41 41 CREATE UNIQUE INDEX I_IND1 ON IND$(OBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 41 EXTENTS (FILE 1 BLOCK 376))
AAAAA7AABAAAAIJAAD 42 42 CREATE INDEX I_ICOL1 ON ICOL$(OBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 42 EXTENTS (FILE 1 BLOCK 384))
AAAAA7AABAAAAIJAAQ 43 43 CREATE UNIQUE INDEX I_FILE1 ON FILE$(FILE#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 43 EXTENTS (FILE 1 BLOCK 392))
AAAAA7AABAAAAIJAAR 44 44 CREATE UNIQUE INDEX I_FILE2 ON FILE$(TS#,RELFILE#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 44 EXTENTS (FILE 1 BLOCK
400))
AAAAA7AABAAAAIKAAI 45 45 CREATE UNIQUE INDEX I_TS1 ON TS$(NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 45 EXTENTS (FILE 1 BLOCK 408))
AAAAA7AABAAAAIKAAT 46 46 CREATE UNIQUE INDEX I_USER1 ON USER$(NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 46 EXTENTS (FILE 1 BLOCK 416))
AAAAA7AABAAAAIKAAU 47 47 CREATE UNIQUE INDEX I_USER2 ON USER$(USER#,TYPE#,SPARE1,SPARE2) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 47 EXTENTS (
FILE 1 BLOCK 424))
AAAAA7AABAAAAIKAAB 48 48 CREATE UNIQUE INDEX I_COL1 ON COL$(OBJ#,NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 32K NEXT 104K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 48 EXTENTS (FILE 1 BLOCK 432))
AAAAA7AABAAAAIKAAC 49 49 CREATE INDEX I_COL2 ON COL$(OBJ#,COL#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 32K NEXT 104K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 49 EXTENTS (FILE 1 BLOCK 440))
AAAAA7AABAAAAIKAAD 50 50 CREATE UNIQUE INDEX I_COL3 ON COL$(OBJ#,INTCOL#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 32K NEXT 104K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 50 EXTENTS (FILE 1 BLOCK 448
))
AAAAA7AABAAAAIJAAF 51 51 CREATE UNIQUE INDEX I_CON1 ON CON$(OWNER#,NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 51 EXTENTS (FILE 1 BLOCK 456
))
AAAAA7AABAAAAIJAAG 52 52 CREATE UNIQUE INDEX I_CON2 ON CON$(CON#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 52 EXTENTS (FILE 1 BLOCK 464))
AAAAA7AABAAAAILAAL 53 53 CREATE UNIQUE INDEX I_CDEF1 ON CDEF$(CON#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 53 EXTENTS (FILE 1 BLOCK 472))
AAAAA7AABAAAAILAAM 54 54 CREATE INDEX I_CDEF2 ON CDEF$(OBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 54 EXTENTS (FILE 1 BLOCK 480))
AAAAA7AABAAAAILAAN 55 55 CREATE INDEX I_CDEF3 ON CDEF$(ROBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 55 EXTENTS (FILE 1 BLOCK 488))
AAAAA7AABAAAAILAAO 56 56 CREATE INDEX I_CDEF4 ON CDEF$(ENABLED) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 56 EXTENTS (FILE 1 BLOCK 496))
AAAAA7AABAAAAIKAAQ 57 57 CREATE INDEX I_CCOL1 ON CCOL$(CON#,COL#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 57 EXTENTS (FILE 1 BLOCK 504))
AAAAA7AABAAAAIKAAR 58 58 CREATE UNIQUE INDEX I_CCOL2 ON CCOL$(CON#,INTCOL#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 58 EXTENTS (FILE 1 BLOCK
512))
AAAAA7AABAAAAIKAAN 59 59 CREATE TABLE BOOTSTRAP$("LINE#" NUMBER NOT NULL,"OBJ#" NUMBER NOT NULL,"SQL_TEXT" VARCHAR2(4000) NOT NULL) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 56K NEXT 1024K MINEXTENTS 1
MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 59 EXTENTS (FILE 1 BLOCK 520))
60 rows selected.
--很明顯 執行順序應該按照line#排列,為什麼oracle要採用這樣的排列模式。
SCOTT@book> @ &r/rowid AAAAA7AABAAAAIJAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
59 1 521 0 1,521 alter system dump datafile 1 block 521 ;
SCOTT@book> @ &r/rowid AAAAA7AABAAAAIKAAN
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
59 1 522 13 1,522 alter system dump datafile 1 block 522 ;
--資料佔用521,522,523 3個資料塊。
SCOTT@book> column PARTITION_NAME noprint
SCOTT@book> select * from dba_extents where owner='SYS' and segment_name='BOOTSTRAP$';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- ------------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SYS BOOTSTRAP$ TABLE SYSTEM 0 1 520 65536 8 1
SCOTT@book> set linesize 2000
SCOTT@book> select * from dba_segments where owner='SYS' and segment_name='BOOTSTRAP$';
OWNER SEGMENT_NAME SEGMENT_TYPE SEGMENT_SU TABLESPACE_NAME HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE RETENTI MINRETENTION PCT_INCREASE FREELISTS FREELIST_GROUPS RELATIVE_FNO BUFFER_ FLASH_C CELL_FL
------ ------------ ------------ ---------- --------------- ----------- ------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ---------- ------- ------------ ------------ ---------- --------------- ------------ ------- ------- -------
SYS BOOTSTRAP$ TABLE MSSM SYSTEM 1 520 65536 8 1 57344 1048576 1 2147483645 2147483645 1 1 1 DEFAULT DEFAULT DEFAULT
--系統表空間使用mssm管理,段分配的第一個塊就是HEADER_BLOCK(注意FREELIST_GROUPS=1)。實際上仔細想想前面這些指令碼並且真正建
--立物件,而是告訴那些物件在檔案的那個位置,透過這樣完成啟動的自舉。
2.探究:
--啟動資料庫到mount狀態,使用10046事件跟蹤:
SYS@book> @ &r/10046on 12
old 1: alter session set events '10046 trace name context forever, level &1'
new 1: alter session set events '10046 trace name context forever, level 12'
Session altered.
SYS@book> alter database open ;
Database altered.
SYS@book> @ &r/10046off ;
Session altered.
--檢查轉儲檔案:
=====================
...
WAIT #139746784093992: nam='db file sequential read' ela= 762 file#=1 block#=520 blocks=1 obj#=-1 tim=1456665187837651
--可以發現一開始讀取file#=1 block#=520 blocks=1 obj#=-1,開始不知道obj#號,直接讀取file#,block#=1,520, blocks=1.
--實際上就是sys.bootstrap$的塊頭。
=====================
PARSING IN CURSOR #139746784085768 len=188 dep=1 uid=0 oct=1 lid=0 tim=1456665187838914 hv=4006182593 ad='858835f0' sqlid='32r4f1brckzq1'
create table bootstrap$ (
--這裡顯示不全。
END OF STMT
PARSE #139746784085768:c=1000,e=1100,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1456665187838912
EXEC #139746784085768:c=1000,e=304,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1456665187839311
CLOSE #139746784085768:c=0,e=7,dep=1,type=0,tim=1456665187839417
=====================
PARSING IN CURSOR #139746784085768 len=55 dep=1 uid=0 oct=3 lid=0 tim=1456665187840200 hv=2111436465 ad='85881c90' sqlid='6apq2rjyxmxpj'
select line#, sql_text from bootstrap$ where obj# != :1
--抽取bootstrap$指令碼,不包括 obj# != 59.
END OF STMT
PARSE #139746784085768:c=999,e=749,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1456665187840199
BINDS #139746784085768:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f19556c82c0 bln=22 avl=02 flg=05
value=59
EXEC #139746784085768:c=1000,e=1322,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=867914364,tim=1456665187841687
WAIT #139746784085768: nam='db file sequential read' ela= 9 file#=1 block#=520 blocks=1 obj#=59 tim=1456665187841775
WAIT #139746784085768: nam='db file scattered read' ela= 21 file#=1 block#=521 blocks=3 obj#=59 tim=1456665187842007
-- 可以從相關等待事件可以確定,先讀bootstrap$開頭,然後從file#=1 block#=521 blocks=3,obj#=59 (這個時候obj#已經知道)
FETCH #139746784085768:c=0,e=362,p=4,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=867914364,tim=1456665187842099
....
FETCH #139746784085768:c=1000,e=12,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=867914364,tim=1456665187843154
.....
FETCH #139746784085768:c=0,e=11,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=867914364,tim=1456665187843208
STAT #139746784085768 id=1 cnt=59 pid=0 pos=1 obj=59 op='TABLE ACCESS FULL BOOTSTRAP$ (cr=61 pr=4 pw=0 time=361 us)'
CLOSE #139746784085768:c=0,e=8,dep=1,type=0,tim=1456665187844815
=====================
--繼續看轉儲檔案:
=====================
PARSING IN CURSOR #139746784087880 len=129 dep=1 uid=0 oct=36 lid=0 tim=1456665187845308 hv=1119914026 ad='7f19555fccb0' sqlid='864bmh11c121a'
CREATE ROLLBACK SEGMENT SYSTEM STORAGE ( INITIAL 112K NEXT 56K MINEXTENTS 1 MAXEXTENTS 32765 OBJNO 0 EXTENTS (FILE 1 BLOCK 128))
END OF STMT
PARSE #139746784087880:c=1000,e=446,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1456665187845306
EXEC #139746784087880:c=0,e=73,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1456665187845460
CLOSE #139746784087880:c=0,e=5,dep=1,type=0,tim=1456665187845525
=====================
--建立system回滾段,實際上仔細看等待事件,根本沒有物理讀取,僅僅告知system的回滾段在(FILE 1 BLOCK 128)。
=====================
PARSING IN CURSOR #139746784087880 len=209 dep=1 uid=0 oct=4 lid=0 tim=1456665187846029 hv=1323908363 ad='7f19556140f8' sqlid='7j058yj7fkg8b'
CREATE CLUSTER C_OBJ#("OBJ#" NUMBER) PCTFREE 5 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 136K NEXT 200K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 2 EXTENTS (FILE 1 BLOCK 144)) SIZE 800
END OF STMT
PARSE #139746784087880:c=0,e=474,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1456665187846028
EXEC #139746784087880:c=2000,e=126,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1456665187846234
CLOSE #139746784087880:c=0,e=5,dep=1,type=0,tim=1456665187846314
=====================
PARSING IN CURSOR #139746784052856 len=191 dep=1 uid=0 oct=9 lid=0 tim=1456665187847251 hv=2739073813 ad='8587dbf8' sqlid='0cmnx32jn5wsp'
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 168))
END OF STMT
PARSE #139746784052856:c=1000,e=911,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=1876228229,tim=1456665187847249
EXEC #139746784052856:c=0,e=271,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1876228229,tim=1456665187847610
STAT #139746784052856 id=1 cnt=0 pid=0 pos=1 obj=0 op='INDEX BUILD UNIQUE I_OBJ# (cr=0 pr=0 pw=0 time=6 us)'
STAT #139746784052856 id=2 cnt=0 pid=1 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=0)'
STAT #139746784052856 id=3 cnt=0 pid=2 pos=1 obj=2 op='TABLE ACCESS FULL C_OBJ# (cr=0 pr=0 pw=0 time=0 us)'
CLOSE #139746784052856:c=0,e=5,dep=1,type=0,tim=1456665187847787
=====================
PARSING IN CURSOR #139746784052856 len=827 dep=1 uid=0 oct=1 lid=0 tim=1456665187848983 hv=4071397944 ad='8587c2d8' sqlid='gsc5dr3tat6js'
CREATE TABLE TAB$("OBJ#"
END OF STMT
PARSE #139746784052856:c=1000,e=1169,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1456665187848982
EXEC #139746784052856:c=0,e=184,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1456665187849253
CLOSE #139746784052856:c=0,e=6,dep=1,type=0,tim=1456665187849373
=====================
PARSING IN CURSOR #139746784052856 len=589 dep=1 uid=0 oct=1 lid=0 tim=1456665187850309 hv=2972122055 ad='8587aab8' sqlid='1vrxtnkskdyy7'
CREATE TABLE CLU$("OBJ#"
END OF STMT
PARSE #139746784052856:c=1000,e=905,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1456665187850308
EXEC #139746784052856:c=0,e=165,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1456665187850562
CLOSE #139746784052856:c=0,e=6,dep=1,type=0,tim=1456665187850668
=====================
PARSING IN CURSOR #139746784052856 len=199 dep=1 uid=0 oct=4 lid=0 tim=1456665187851153 hv=3248999241 ad='7f19555fcbe0' sqlid='gtdvd3b0ugku9'
CREATE CLUSTER C_TS#("TS#" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 6 EXTENTS (FILE 1 BLOCK 176))
END OF STMT
PARSE #139746784052856:c=0,e=450,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1456665187851152
EXEC #139746784052856:c=0,e=104,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1456665187851350
CLOSE #139746784052856:c=0,e=5,dep=1,type=0,tim=1456665187851425
=====================
PARSING IN CURSOR #139746784052856 len=189 dep=1 uid=0 oct=9 lid=0 tim=1456665187852170 hv=1732390603 ad='85878aa8' sqlid='2k93zvdmn4bqb'
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 184))
END OF STMT
PARSE #139746784052856:c=1000,e=720,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=2460502694,tim=1456665187852169
EXEC #139746784052856:c=0,e=144,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2460502694,tim=1456665187852439
STAT #139746784052856 id=1 cnt=0 pid=0 pos=1 obj=0 op='INDEX BUILD UNIQUE I_TS# (cr=0 pr=0 pw=0 time=1 us)'
STAT #139746784052856 id=2 cnt=0 pid=1 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=0)'
STAT #139746784052856 id=3 cnt=0 pid=2 pos=1 obj=6 op='TABLE ACCESS FULL C_TS# (cr=0 pr=0 pw=0 time=0 us)'
CLOSE #139746784052856:c=0,e=5,dep=1,type=0,tim=1456665187852600
=====================
--仔細看根本沒有相關的物理讀取等待事件,也就是這些建立語句僅僅告之oracle這些表應該到那裡查詢。
...
=====================
PARSING IN CURSOR #139746784052856 len=205 dep=1 uid=0 oct=9 lid=0 tim=1456665187914094 hv=2729474672 ad='7cbcdf88' sqlid='9a9j7rqjb0ymh'
CREATE UNIQUE INDEX I_CCOL2 ON CCOL$(CON#,INTCOL#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 58 EXTENTS (FILE 1 BLOCK 512))
END OF STMT
PARSE #139746784052856:c=0,e=753,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=1536140006,tim=1456665187914092
EXEC #139746784052856:c=1000,e=133,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1536140006,tim=1456665187914354
STAT #139746784052856 id=1 cnt=0 pid=0 pos=1 obj=0 op='INDEX BUILD UNIQUE I_CCOL2 (cr=0 pr=0 pw=0 time=1 us)'
STAT #139746784052856 id=2 cnt=0 pid=1 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=0)'
STAT #139746784052856 id=3 cnt=0 pid=2 pos=1 obj=32 op='TABLE ACCESS FULL CCOL$ (cr=0 pr=0 pw=0 time=0 us)'
CLOSE #139746784052856:c=0,e=6,dep=1,type=0,tim=1456665187914511
=====================
--直到執行LINE#=58,也沒有物理讀取操作。
3. oralce 如何直到要從file#1,block#=520上讀取呢?實際上這個資訊記錄file#1,block#=1上。
--回頭看轉載的開始的等待事件,每個資料檔案都要讀取block#=1
WAIT #139746784093992: nam='db file sequential read' ela= 9 file#=1 block#=1 blocks=1 obj#=-1 tim=1456665186803089
WAIT #139746784093992: nam='db file sequential read' ela= 9 file#=2 block#=1 blocks=1 obj#=-1 tim=1456665186803160
WAIT #139746784093992: nam='db file sequential read' ela= 8 file#=3 block#=1 blocks=1 obj#=-1 tim=1456665186803225
WAIT #139746784093992: nam='db file sequential read' ela= 7 file#=4 block#=1 blocks=1 obj#=-1 tim=1456665186803270
WAIT #139746784093992: nam='db file sequential read' ela= 7 file#=5 block#=1 blocks=1 obj#=-1 tim=1456665186803316
WAIT #139746784093992: nam='db file sequential read' ela= 9 file#=6 block#=1 blocks=1 obj#=-1 tim=1456665186803361
--透過bbed觀察:
BBED> set dba 1,1
DBA 0x00400001 (4194305 1,1)
BBED> p kcvfh.kcvfhrdb
ub4 kcvfhrdb @96 0x00400208
BBED> set dba 1,520
DBA 0x00400208 (4194824 1,520)
--在偏移量96記錄塊地址dba=0x00400208,正好等於set dba 1,520看到的結果。
--也可以轉儲檔案頭看看:
alter session set events 'immediate trace name FILE_HDRS level 12';
Tablespace #0 - SYSTEM rel_fn:1
Creation at scn: 0x0000.00000007 08/24/2013 11:37:33
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x35711eb0 scn: 0x0000.000e2006
prev reset logs count:0x3121c97a scn: 0x0000.00000001
recovered at 02/26/2016 08:54:45
status:0x2004 root dba:0x00400208 chkpt cnt: 692 ctl cnt:691
~~~~~~~~~~~~~~
begin-hot-backup file size: 0
Checkpointed at scn: 0x0003.144efb17 02/28/2016 21:13:07
thread:1 rba:(0x1a3.8d69.10)
--注意看~的部分,也可以確定。
$ bvi -b 8192 -s 8192 /mnt/ramdisk/book/system01.dbf
00002000 0B A2 00 00 01 00 40 00 00 00 00 00 00 00 01 04 ......@.........
00002010 96 D3 00 00 00 00 00 00 00 04 20 0B 6E 21 B7 4F .......... .n!.O
00002020 42 4F 4F 4B 00 00 00 00 BD C3 00 00 00 7C 01 00 BOOK.........|..
00002030 00 20 00 00 01 00 03 00 00 00 00 00 00 00 00 00 . ..............
00002040 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00002050 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00002060 08 02 40 00 07 00 00 00 00 00 00 00 7D C9 21 31 ..@.........}.!1
~~~~~~~~~~~
00002070 B0 1E 71 35 06 20 0E 00 00 00 00 00 00 00 00 00 ..q5. ..........
00002080 00 00 00 00 00 00 00 00 00 00 04 20 B4 02 00 00 ........... ....
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2016219/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【BBED】 sys.bootstrap$ 物件的恢復boot物件
- [20171203]關於raw型別.txt型別
- [20160213]關於ansi語法.txt
- [20170406]關於檔案頭轉儲.txt
- [20170926]關於linux重定向.txtLinux
- [20171201]關於explain plan.txtAI
- [20171115]關於namespace.txtnamespace
- [20170724]關於sql_id那些事.txtSQL
- [20120903]關於Virtual index.txtIndex
- [20190821]關於CPU成本計算.txt
- [20210410]關於time命令的解析.txt
- [20210527]關於v$wait_chain.txtAI
- [20161128]關於Little Enddian.txt
- [20150402]關於SQL Monitoring.txtSQL
- [20141205]關於sql_id.txtSQL
- [20160219]關於連線順序.txt
- [20150503]關於oracle的number型別.txtOracle型別
- [20150505]關於alert.log檔案.txt
- [20150616]關於sql_id.txtSQL
- [20190401]關於semtimedop函式呼叫.txt函式
- [20181229]關於字串的分配問題.txt字串
- [20160831]關於資料塊Checksum.txt
- [20161214]關於Buffer Busy Waits.txtAI
- [20150325]關於引數archive_lag_target.txtHive
- [20141228]關於bloom filter.txtOOMFilter
- [20121121]關於linux下history命令.txtLinux
- [20190918]關於函式索引問題.txt函式索引
- [20180912]關於ANSI joins語法.txt
- [20210926]關於pam_tally2模組.txt
- [20161021]關於sqlplus sqlprompt設定.txtSQL
- [20170611]關於資料塊地址的計算.txt
- [20171202]關於函式索引的狀態.txt函式索引
- [20140125]關於標量子查詢.txt
- [20150304]關於sql格式化問題.txtSQL
- [20141219]關於sqlnet.expire_time.txtSQL
- [20151208]關於Oracle Row Lock.txtOracle
- [20150910]關於物件級檢查點.txt物件
- [20150810]關於提示DRIVING_SITE.txt