Oracle Table 创建参数说明

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

先看一个oracle 10g 下table 创建sql,都是默认值：
create table sys.qs
（
username  varchar2（30 byte）                  not null,
user_id  number                              not null,
created  date                                not null
）
tablespace system
pctused    40
pctfree    10
initrans  1
maxtrans  255
storage    （
initial          64k
minextents      1
maxextents      unlimited
pctincrease      0
freelists        1
freelist groups  1
buffer_pool      default
）
logging
nocompress
nocache
noparallel
monitoring;
对于 数据字典管理（dictionary managed）和 本地化管理（local managed）的表空间，他们的参数是不同的，， 在local managed 模式下，的autoallocate和 uniform类型不同，参数也会不同。 这里使用的是local managed autoallocate类型的表空间。

一、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 to size_clause for information on that clause.
In locally managed tablespaces, Oracle uses the value of INITIAL, in conjunction with the type of local management—AUTOALLOCATE or UNIFORM—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 INITIAL You cannot specify INITIAL in an ALTER statement.

LibLib AI

中国领先原创AI模型分享社区，拥有LibLib等于拥有了超多模型的模型库、免费的在线生图工具，不考虑配置的模型训练工具

647

查看详情

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.
（11）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 view.
（22）In a dictionary-managed tablespace, MINEXTENTS is simply the minimum number of extents that must be allocated to the segment.
If the MINEXTENTS value is greater than 1, then Oracle calculates the size of subsequent extents based on the values of the INITIAL, NEXT, and PCTINCREASE storage parameters.
When changing the value of MINEXTENTS by specifying it in an ALTER statement, you can reduce the value from its current value, but you cannot increase it. Resetting MINEXTENTS to a smaller value might be useful, for example, before a TRUNCATE … DROP STORAGE statement, if you want to ensure that the segment will maintain a minimum number of extents after the TRUNCATE operation.
Restrictions on MINEXTENTS
The MINEXTENTS storage parameter is subject to the following restrictions:
（11）MINEXTENTS is not applicable at the tablespace level.
（22）You cannot change the value of MINEXTENTS in an ALTER statement or for an object that resides in a locally managed tablespace.