dba_tablespace和dba_segments视图中的initial_extent

INITIAL_EXTENT：Size in bytes requested for the initial extent of the segment at create time. (Oracle rounds the extent size to multiples of 5 blocks if the requested size is greater than 5 blocks.)

初始区的大小，如果要求大于5块则设置成是5个块的大小

SQL> alter system set deferred_segment_creation=false scope=both;

System altered.

SQL> create table scott.init_tab1(a int);

Table created.

SQL> select segment_name,initial_extent/8192,blocks from dba_segments where segment_name='INIT_TAB1';

SEGMENT_NAME INITIAL_EXTENT/8192 BLOCKS

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

INIT_TAB1   8     8

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL> show parameter db_block_size;

NAME     TYPE VALUE

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

db_block_size     integer 8192

很奇怪为什么创建空表initial_extent是8个块的大小

SQL> create index init_indx on scott.init_tab1(a);

Index created.

SQL>  select segment_name,segment_type,initial_extent/8192,blocks from dba_segments where segment_name='INIT_INDX';

SEGMENT_NAME SEGMENT_TYPE   INITIAL_EXTENT/8192   BLOCKS

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

INIT_INDX INDEX     8 8

SQL> create table init_lob(a varchar(2),b clob);

Table created.

SQL> col object_name for a30;

SQL> select object_name,created from dba_objects where to_char(created,'yyyymmdd')='20170621' and object_type='LOB';

OBJECT_NAME       CREATED

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

SYS_LOB0000075634C00002$$      2017-06-21 16:45:06

SYS_LOB0000075627C00002$$      2017-06-21 13:33:57

SQL>  select segment_name,segment_type,initial_extent/8192,blocks from dba_segments where segment_name='SYS_LOB0000075634C00002$$';

SEGMENT_NAME SEGMENT_TYPE   INITIAL_EXTENT/8192   BLOCKS

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

SYS_LOB00000756 LOBSEGMENT     8 8

34C00002$$

确实是8个initial blocks。

SQL> select segment_name,segment_type,initial_extent/8192,blocks from dba_segments where segment_name='INIT_TABLE';

SEGMENT_NAME SEGMENT_TYPE   INITIAL_EXTENT/8192   BLOCKS

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

INIT_TABLE TABLE     2 8

SQL>  select segment_name,segment_type,initial_extent/8192,blocks from dba_segments where segment_name='INIT_TABLE1';

SEGMENT_NAME SEGMENT_TYPE   INITIAL_EXTENT/8192   BLOCKS

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

INIT_TABLE1 TABLE 25600     25600

指定了大小，跟5没有任何关系。

SQL> select tablespace_name,initial_extent/8192 from dba_tablespaces;

TABLESPACE_NAME        INITIAL_EXTENT/8192

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

SYSTEM 8

SYSAUX 8

UNDOTBS1 8

TEMP       128

USERS 8

这里有表空间的initial_extent默认值，看上去这就是为什么是8而不是5的原因，我们来试着来更改这个默认值

SQL> alter tablespace users default storage (initial 5k);

alter tablespace users default storage (initial 5k)

*

ERROR at line 1:

ORA-25143: default storage clause is not compatible with allocation policy

居然改不了，找到了相关MOS (文档 ID 123470.1)，有如下解释

cause: You cannot give a default storage clause to a local managed tablespace.

The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT 

STORAGE are not valid for locally managed tablespaces.

locally managed tablespaces

当表空间是local管理时不能修改storage。

看看DBA_TABLESPACE.EXTENT_MANAGEMENT字段的解释

Indicates whether the extents in the tablespace are dictionary managed (DICTIONARY) or locally managed (LOCAL)；

SQL> select tablespace_name,initial_extent,extent_management from dba_tablespaces;

TABLESPACE_NAME        INITIAL_EXTENT EXTENT_MAN

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

SYSTEM 65536 LOCAL

SYSAUX 65536 LOCAL

UNDOTBS1 65536 LOCAL

TEMP       1048576 LOCAL

USERS 65536 LOCAL

CORRUPT_T 65536 LOCAL

COR_TBS 65536 LOCAL

7 rows selected.

全是local management，那咱们来试着创建一个字典管理表空间

SQL> create tablespace tbs_dict datafile '/u01/app/oracle/oradata/test/tbs_dict.dbf' size 200m extent management dictionary;

create tablespace tbs_dict datafile '/u01/app/oracle/oradata/test/tbs_dict.dbf' size 200m extent management dictionary

*

ERROR at line 1:

ORA-12913: Cannot create dictionary managed tablespace

[oracle@lzl ~]$ oerr ora 12913 

12913, 00000, "Cannot create dictionary managed tablespace"

// *Cause: Attemp to create dictionary managed tablespace in database 

//         which has system tablespace as locally managed

// *Action: Create a locally managed tablespace.

如果SYSTEM表空间是local管理，不能创建dictionary管理模式的表空间。

看来表空间还有很多东西要学，下面再讲述DBA_TABLESPACE和extent_management。

至少我们现在明白了，永久表空间的initial_extent默认值是8个块的size，不是5，且SYSTEM表空间是local管理时不能随意更改表空间的extent_management，local模式不能更改表空间的initial_extent默认值。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31461640/viewspace-2141103/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31461640/viewspace-2141103/