关于数据库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 MAXEXTENTS 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 1024KMINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 8 EXTENTS (FILE 1 BLOCK73)) SIZE 225

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 BLOCK 81))

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 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#)

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 NOT 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,"AVGCHN" NUMBER,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" NUMBER,"SPARE5" VARCHAR2(1000),"SPARE6" VARCHAR2(1000),"SPARE7" DATE) STORAGE ( OBJNO 5TABNO 2) CLUSTER C_OBJ#(OBJ#)

6

CREATE 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))

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 OBJNO7 EXTENTS (FILE 1 BLOCK 65))

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) 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))

36

CREATE UNIQUE 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

由这里我们可以看出，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 S

• ·本地分区索引如何确定表空间(1)
• ·销售订单单行状态不对订单不关闭的处理
• ·A little test for index
• ·Oracle数据库诊断性能问题
• ·使用Explain进行查询及应用优化
• ·关于数据库调优Node1
• ·我对ORACLE数据锁的一点体会
• ·在*.SQL文件里获取并使用变量
• ·各个平台上调整最大SGA区的方法
• ·Windows下常见Oracle服务介绍