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