SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area 131142648 bytesFixed Size 451576 bytesVariable Size 104857600 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesDatabase mounted.SQL> alter session set sql_trace = true; Session altered.sql> ALTABASE Open; Database Altered.sql> First we come to the front part of the trace file (reference attachment) This is the first object of Creating Create Table Bootstrap $ (Line # Number Not Null, Obj # Number Not NULL, SQL_TEXT VARCHAR2 (4000) NOT NULL) Storage (FILE 1 Block 377)) Next, let's see SELECT LINE #, SQL_Text FromBootstrap $ Where Obj #! =: 1 (here actually It is the structure of gradually extracting content to establish a dictionary table itself. According to the following contents, you can see it here) So we are here to see Bootstap $ SQL> Desc Bootstrap $ NAME NULL? TYPE ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------- -------------- Line # not null number # NOT NUMBERSQL_TEXT NOT NUMBERSQL_TEXT NOT NULL VARCHAR2 (4000) SQL> SQL> SELECT Count (*) from bootstrap $; 57sql> SQL> Select Obj #, SQL_Text from Bootstrap $ Where Rownu m <11; -18.0.0.0.00CREATE ROLLBACK SEGMENT SYSTEM STORAGE (INITIAL 112K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 32765 OBJNO 0 EXTENTS (FILE 1 BLOCK 9)) 8CREATE CLUSTER C_FILE # _BLOCK # ( "TS #" NUMBER, "SEGFILE #" NUMBER, "SEGBLOCK #" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 24K NEXT 1024KMINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 8 EXTENTS (FILE 1 BLOCK73)) SIZE 2259CREATE INDEX I_FILE # _BLOCK # ON CLUSTER C_FILE # _BLOCK # PCTFREE 10 INITRANS 2 MAXTRANS 255 Storage (Initial 64k Next) 2147483645 Pctincrease 0 Objno 9 Extents (File 1 Block 81)) 14CRREATE TABLE SEG $ ("File #" Number Not Null, "Block #"
Number Not Null, "Type #" Number Not Null, "TS #" Number Not Null, "Blocks" Number Not Null, "Iniexts" Number Not Null, "Minexts" Number Not Null, "maxexts" Number NOT NULL, "MAXEXTS "Number NOT NULL," Extpct "Number Not Null," User # "Number Not Null," Lists "Number," Groups "Number," BitmapRanges "Number Not Null," Cachehint "Number Not 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 #) 5create 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 Not Null," Initrans "Number Not Null," Size $ "Number," Hashfunc "VARCHAR2 (30)," Hashkeys "Number," Func "Number "Extind" Number, "Flags" Number, "Degree" Number, "Avgchn" Number, "Spare1" Number, "Spare2" Number, "Spare3" Number, "Spare4" Number, "Spare5" VA, "Spare5" VA, "SPARE5" VA Rchar2 (1000), "Spare6" VARCHAR2 (1000), "Spare7" Date) Storage (Objno 5tabno 2) Cluster C_OBJ # (Obj #) 6CREATE Cluster C_TS # ("TS #"
NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255STORAGE (INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 6 EXTENTS (FILE 1 BLOCK 57)) 7CREATE INDEX I_TS # ON CLUSTER C_TS # PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO7 EXTENTS (FILE 1 BLOCK 65)) 18CREATE 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, "STATUS" Number NOT NULL, "Remoteowner" VARCHAR2 (30), "LINKNAME" varcha2 (128), "Flags" Number, "OID $" RAW (16), "spare1" Number, "spare2" Number, "spare3" Number, " SPARE4 "VARCHAR2 (1000)," SPARE5 "VARCHAR2 (1000)," SPARE6 "DATE) PCTFREE10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (INITIAL 16K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 18 EXTENTS (FILE 1 BLOCK 121)) 36CREATEUNIQUE INDEX I_OBJ1 ON OBJ $ (OBJ #) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 36 EXTENTS (FILE 1 BLOCK 217)) 10 rows selected.SQL> Here we can see the Bootstrap $ actually records some basic objects of some database system basic objects. For our database, you can also see the establishment of a table structure, through this structure, you can get data in the file by relational database, OK, let's take a look at the content in the trace file, found Create Rollback Segment System Storage (Initial 112k Next 1024K Minelt (Initial 112k Next 102765 Objno 0 Extens (File 1 Block 9)) created a system back in the head of the database system table space Gift (start, 1 --- 8 belonging to data file)
CREATE then down 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 where the same is directly specifies the segment block of the cluster head position of 25SQL> select file_id, block_id from dba_extents where segment_name = 'C_OBJ #'; 1 251 331 411 32411 44411 44731 44891 45131 45291 45611 45851 46091 46411 46811 47531 48331 48731 126811 236891 2624920 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, "Flags" Number Not Null, "Audit $" VARCHAR2 (38) NOT Null, "Rowcnt" Number, "EMPCNT" Number, "EMPCNT" Number, "Avgspc" 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 #) We can see that from Tab $ start, store changes, Storage (Objno 4 Tabno 1) Cluster C_OBJ # (Obj #) The Objno 4 here What does Tabno 1 mean? (through Cluster C_OBJ # (Obj #) We can find table Tab $ itself) 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 analysis -------------- ------ ----------------------------- ----- Degree Instances Intcols KernelCols Property Trigflag SPARE1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - - ---------- ---------- ---------------------------------------------------------------------------------------------------------------------------------- ---- Spare4 --------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - -------------------------------------------------- --------------- Spare6 --------- 4 2 0 1 25 2 137 1 0 0 0 0 17 ------------ -------------------- 921 428 832381 0 139 6368 7 11-Aug-03 92137 37 1024 0 006-Aug-03sql> From here you can see, storage parameters From here, take it from the record of fixed tables in the data file, and then look forward to the Trace file, it will find that the database can be extracted from the system tablespace file from the system table space file. we see above definition CREATE stored 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 The extens is indicated by Block 25, which we assume that DB_BLOCK_SIZE is not set in the initialization parameter file, and it will inevitably lead to the problem of file location positioning errors to study this problem (formerly studying today),
Because I first think about such a problem, we have to query a table of your own system, first of all, find the structured information of the table in the data dictionary, these structural information stored in the data dictionary table, but where to get data Dictionary table itself structure information? Although we can check the structured information of the Dictionary table itself from the table, how is the structure in the database Open? Is it written in the program? Still how to do it. From the above, we can see that as long as the program provides the creation script of Bootstrap $, determine the structure of the Bootstap $ specifies the location of the segment, and then gets the contents of Bootstrap $ via the segment, and these content is Create SQL_Text for some dictionary tables, which has incubated C_OBJ #, Tab $ such as C_OBJ #, TAB $, etc., which has completed the structure of the database dictionary in the Bootstrap $. That is to say, Oracle uses a relationship form to achieve the establishment of its own structure. One question you should pay attention to here is that the CREATE is in the startup process, not physically to create a segment, but created a corresponding structure in memory, and can get the system table space according to this structure. information. That is, in order to generate TAB $, the SQL is from bootstrap $, and the starter, Oracle is created in SGA, you can go to System TableSpace to get Bootstrap $ in SGA. The content (and this Create's statement can be placed in the executable program or the DLL as a natural egg), then gradually completes the establishment of the Dictionary in SGA. Of course, all these processes have not truly create physical objects.
About TRACE files, you can use the TRACE session in the mount, TKPROF format the trace file to get
Original discussion reference: http://www.itpub.net/showthread.php?threadid=199099
