oracle table nocache,Oracle Table 创建参数 说明

Oracle Table 创建参数 说明

先看一个10g下table创建SQL,都是默认值：

CREATETABLESYS.QS

(

USERNAMEVARCHAR2(30BYTE)NOTNULL,

USER_IDNUMBERNOTNULL,

CREATEDDATENOTNULL

)

TABLESPACESYSTEM

PCTUSED40

PCTFREE10

INITRANS1

MAXTRANS255

STORAGE(

INITIAL64K

MINEXTENTS1

MAXEXTENTSUNLIMITED

PCTINCREASE0

FREELISTS1

FREELISTGROUPS1

BUFFER_POOLDEFAULT

)

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

对于 数据字典管理(Dictionary managed)和 本地化管理(Local Managed)的表空间，他们的参数是不同的， 在Local Managed模式下，的autoallocate和uniform类型不同，参数也会不同。 这里使用的是local managed autoallocate类型的表空间。

Oracle表空间创建参数说明

官网链接：

storage_clause

CREATE TABLE

表空间(tableSpace)段(segment)盘区(extent)块(block)关系

一.Storage参数说明

1. INITIAL

Specify

the size of the first extent of the object. Oracle allocates space for

this extent when you create the schema object. Refer tofor information on that clause.

Inlocally managed tablespaces, Oracle uses the value of INITIAL, in conjunction with the type of local management—AUTOALLOCATE orUNIFORM—and the values of MINEXTENTS, NEXT and PCTINCREASE, to determine the initial size of the segment.

(1).With AUTOALLOCATE extent management, Oracle uses the INITIAL setting to optimize the number of extents allocated.Extents of 64K, 1M, 8M, and 64M can be allocated.During segment creation,the

system chooses the greatest of these four sizes that is equal to or

smaller than INITIAL, and allocates as many extents of that size as are

needed to reach or exceed the INITIAL setting.For example, if

you set INITIAL to 4M, then the database creates four 1M extents. But if

you set INITIAL to 14M, then the database creates two 8M extents, which

exceeds the INITIAL setting, rather than creating the less optimal one

8M extent plus six 1M extents.

(2).For

UNIFORM extent management, the number of extents is determined from

initial segment size and the uniform extent size specified at tablespace

creation time. For example, in a uniform

locally managed tablespace with 1M extents, if you specify an INITIAL

value of 5M, then Oracle creates five 1M extents.

Consider this comparison:With AUTOALLOCATE, if you set INITAL to 72K, then the initial segment

size will be 128K (greater than INITIAL). The database cannot allocate

an extent smaller than 64K, so it must allocate two 64K extents.If you set INITIAL to 72K with a UNIFORM extent size of 24K, then the database will allocate three 24K extents to equal 72K.

In dictionary managed tablespaces, the default initial extent size is 5 blocks,and all subsequent extents are rounded to 5 blocks.If MINIMUM EXTENT was specified at tablespace creation time, then the extent sizes are rounded to the value of MINIMUM EXTENT.

--自Oracle 9i以后，推荐使用本地管理的表空间，不建议使用字典管理的表空间。

Restriction on INITIALYou cannot specify INITIAL in an ALTER statement.

2.MINEXTENTS

(1)In locally managed tablespaces,Oracle Database uses the value of MINEXTENTS in conjunction with

PCTINCREASE, INITIAL and NEXT to determine the initial segment size.

(2)In dictionary-managed tablespaces,specify the total number of extents to allocate when the object is created.The default and minimum value is 1,meaning that Oracle allocates only the initial extent, except for

rollback segments, for which the default and minimum value is 2. The

maximum value depends on your operating system.

(1).In a locally managed tablespace,MINEXTENTS is used to compute the initial amount of space allocated,which is equal to INITIAL * MINEXTENTS.Thereafter this value is set to 1, which is reflected in the DBA_SEGMENTS vi