Oracle Table创建参数说明

我就是我 2021-06-26 16:06 358阅读 0赞

先看一个`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.

指定对象的第一个范围的大小。 在创建架构对象时，Oracle会为此范围分配空间。 有关该子句的信息，请参阅size\_clause。  
在本地管理的表空间中，Oracle使用INITIAL的值以及本地管理类型 - AUTOALLOCATE或UNIFORM-以及MINEXTENTS，NEXT和PCTINCREASE的值来确定段的初始大小。  
（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.  
通过AUTOALLOCATE扩展区管理，Oracle使用INITIAL设置来优化分配的扩展区数。 可以分配64K，1M，8M和64M的范围。 在段创建期间，系统选择这四个大小等于或小于INITIAL的最大值，并分配达到或超过INITIAL设置所需的那个大小的范围。 例如，如果将INITIAL设置为4M，则数据库将创建四个1M范围。 但是如果将INITIAL设置为14M，则数据库会创建两个8M范围，这超出了INITIAL设置，而不是创建不太理想的一个8M范围加上六个1M范围。  
（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.

1.  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.
2.  MAXEXTENTS  
    This storage parameter is valid only for objects in dictionary-managed tablespaces. Specify the total number of extents, including the first, that Oracle can allocate for the object. The minimum value is 1 except for rollback segments, which always have a minimum of 2. The default value depends on your data block size.  
    Restriction on MAXEXTENTS  
    MAXEXTENTS is ignored for objects residing in a locally managed tablespace, unless the value of ALLOCATION\_TYPE is USER for the tablespace in the DBA\_TABLESPACES data dictionary view.  
    –该参数在local managed tablespace 是忽略的。  
    UNLIMITED  
    Specify UNLIMITED if you want extents to be allocated automatically as needed. Oracle recommends this setting as a way to minimize fragmentation.  
    Do not use this clause for rollback segments. Doing so allows the possibility that long-running rogue DML transactions will continue to create new extents until a disk is full.  
    Caution:  
    A rollback segment that you create without specifying the storage\_clause has the same storage parameters as the tablespace in which the rollback segment is created. Thus, if you create a tablespace with MAXEXTENTS UNLIMITED, then the rollback segment will have this same default.
3.  PCTINCREASE  
    （1）In locally managed tablespaces, Oracle Database uses the value of PCTINCREASE during segment creation to determine the initial segment size and ignores this parameter during subsequent space allocation.  
    （2）In dictionary-managed tablespaces, specify the percent by which the third and subsequent extents grow over the preceding extent. The default value is 50, meaning that each subsequent extent is 50% larger than the preceding extent. The minimum value is 0, meaning all extents after the first are the same size. The maximum value depends on your operating system. Oracle rounds the calculated size of each new extent to the nearest multiple of the data block size. If you change the value of the PCTINCREASE parameter by specifying it in an ALTER statement, then Oracle calculates the size of the next extent using this new value and the size of the most recently allocated extent.  
    Restriction on PCTINCREASE  
    You cannot specify PCTINCREASE for rollback segments. Rollback segments always have a PCTINCREASE value of 0.
4.  FREELISTS  
    （1）In tablespaces with manual segment-space management, Oracle Database uses the FREELISTS storage parameter to improve performance of space management in OLTP systems by increasing the number of insert points in the segment.  
    （2）In tablespaces with automatic segment-space management, this parameter is ignored, because the database adapts to varying workload.  
    In tablespaces with manual segment-space management, for objects other than tablespaces and rollback segments, specify the number of free lists for each of the free list groups for the table, partition, cluster, or index. The default and minimum value for this parameter is 1, meaning that each free list group contains one free list. The maximum value of this parameter depends on the data block size. If you specify a FREELISTS value that is too large, then Oracle returns an error indicating the maximum value.  
    This clause is not valid or useful if you have specified the SECUREFILE parameter of LOB\_parameters. If you specify both the SECUREFILE parameter and FREELISTS, then the database silently ignores the FREELISTS specification.  
    Restriction on FREELISTS  
    You can specify FREELISTS in the storage\_clause of any statement except when creating or altering a tablespace or rollback segment.
5.  FREELIST GROUPS  
    （1）In tablespaces with manual segment-space management, Oracle Database uses the value of this storage parameter to statically partition the segment free space in an Oracle Real Application Clusters environment. This partitioning improves the performance of space allocation and deallocation by avoiding inter instance transfer of segment metadata. In tablespaces with automatic segment-space management, this parameter is ignored, because Oracle dynamically adapts to inter instance workload.  
    （2）In tablespaces with manual segment-space management, specify the number of groups of free lists for the database object you are creating. The default and minimum value for this parameter is 1. Oracle uses the instance number of Oracle Real Application Clusters （Oracle RAC） instances to map each instance to one free list group.  
    Each free list group uses one database block. Therefore:  
    （11）If you do not specify a large enough value for INITIAL to cover the minimum value plus one data block for each free list group, then Oracle increases the value of INITIAL the necessary amount.  
    （22）If you are creating an object in a uniform locally managed tablespace, and the extent size is not large enough to accommodate the number of freelist groups, then the create operation will fail.  
    This clause is not valid or useful if you have specified the SECUREFILE parameter of LOB\_parameters. If you specify both the SECUREFILE parameter and FREELIST GROUPS, then the database silently ignores the FREELIST GROUPS specification.  
    Restriction on FREELIST GROUPS  
    You can specify the FREELIST GROUPS parameter only in CREATE TABLE, CREATE CLUSTER, CREATE MATERIALIZED VIEW, CREATE MATERIALIZED VIEW LOG, and CREATE INDEX statements.
6.  BUFFER\_POOL  
    The BUFFER\_POOL clause lets you specify a default buffer pool or cache for a schema object. All blocks for the object are stored in the specified cache.  
    （1）If you define a buffer pool for a partitioned table or index, then the partitions inherit the buffer pool from the table or index definition unless overridden by a partition-level definition.  
    （2）For an index-organized table, you can specify a buffer pool separately for the index segment and the overflow segment.  
    Restrictions on the BUFFER\_POOL Parameter  
    BUFFER\_POOL is subject to the following restrictions:  
    （1）You cannot specify this clause for a cluster table. However, you can specify it for a cluster.  
    （2）You cannot specify this clause for a tablespace or a rollback segment.  
    KEEP  
    Specify KEEP to put blocks from the segment into the KEEP buffer pool. Maintaining an appropriately sized KEEP buffer pool lets Oracle retain the schema object in memory to avoid I/O operations. KEEP takes precedence over any NOCACHE clause you specify for a table, cluster, materialized view, or materialized view log.  
    RECYCLE  
    Specify RECYCLE to put blocks from the segment into the RECYCLE pool. An appropriately sized RECYCLE pool reduces the number of objects whose default pool is the RECYCLE pool from taking up unnecessary cache space.  
    DEFAULT  
    Specify DEFAULT to indicate the default buffer pool. This is the default for objects not assigned to KEEP or RECYCLE.