oracle之数据字典屣履造门(四)

新值 2: where x.indx=y.indx and x.ksppinm like '%db_block_max_scan%'

NAME VALUE PDESC

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

_db_block_max_scan_pct 50 Percentage of buffers to inspect when looking for

free

SYS@orcl#

可以看到我们修改的实施在spfile中加入隐含参数，然后在数据启动初始化的时候会引用隐含参数的值。（不建议修改）

SYS@orcl#alter system reset "_db_block_max_scan_pct";

系统已更改。

SYS@orcl#startup force;

2）数据字典表：

在创建数据库的时候安装sql.bsp文件进行创建，在数据库启动的时候进行先关字典表的创建。可以通过跟踪数据库启动的过程进行分析。

eg:

startup nomount;

alter session set events '10046 trace nam context level 12';

alter database mount;

alter database open;

如下是截取的跟踪文件信息：

509 CREATE TABLE USER$("USER#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"TYPE#" NUMBER NOT NULL,"PASSWORD" VARCHAR2(30),"DATATS#" NUMBER NOT NULL,"TEMPTS#" NUM BER 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,"SPA RE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) STORAGE ( OBJNO 22 TABNO 1) CLUSTER C_USER#(USER#)

510 END OF STMT

511 PARSE #1:c=0,e=456,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1371736252499680

512 BINDS #1:

513 EXEC #1:c=0,e=217,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1371736252499956

=====================

480 PARSING IN CURSOR #1 len=637 dep=1 uid=0 oct=1 lid=0 tim=1371736252496043 hv=1077251290 ad='8fd93c08' sqlid='3cgdmzx03b36u'

481 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" V ARCHAR2(128),"FLAGS" NUMBER,"OID$" RAW(16),"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) PCTFR EE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 16K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 18 EXTENTS (FILE 1 BLOCK 121) )

482 END OF STMT

483 PARSE #1:c=0,e=552,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1371736252496039

484 BINDS #1:

485 EXEC #1:c=0,e=205,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1371736252496308

当我们创建表的时候，其实是向先关数据字典写入数据，分别是obj$,con$,col$,tab$等等。可以跟踪sql语句进行分析。

当然了我们创建完表之后，可以通过包进行查看我们当时的ddl语句。

eg：

SYS@orcl#select table_name from user_tables where table_name='T';

TABLE_NAME

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

T

SYS@orcl#select dbms_metadata.get_ddl('TABLE','T') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T')

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

CREATE TABLE "SYS"."T"

( "TABLESPACE_NAME" VARCHAR2(30) NOT NULL ENABLE,

SYS@orcl#SET LONG 20000

SYS@orcl#R

1* select dbms_metadata.