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 view.

(2).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:

(1).MINEXTENTS is not applicable at the tablespace level.

(2).You cannot change the value of MINEXTENTS in an ALTER statement or for an object that resides in a locally managed tablespace.

3. 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

MAXEXTENTSis ignored for objects residing in a locally managed tablespace, unless the value of ALLOCATION_TYPE is USER for the tablespacein 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.

4.PCTINCREASE

(1)In locally managed tablespaces,Oracle Database uses the value of PCTINCREASE during segment creation to determine the initial segment size andignores 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.

5. 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. 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.

6. 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:

(1).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.

(2).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.

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.

7. 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.

二.其他参数说明

1.Pctfree： 默认值是10，如果数据块的使用率高于pctfree的值，则该数据块从freelist中移出。

2. Pctused：默认值是40，如果数据块的使用率小于pctused的值，则该数据块重新加入到fresslist中。Pctfree, Pctused是互相消涨的,其和不能超过100

3. Initrans： 默认值1，该参数表示在单一块中最初活动的交易事务数。

4. Maxtrans：默认值是255，表示在单一块中最大交易事务数。

5. logging_clause

Specify

whether the creation of the table and of any indexes required because

of constraints, partition, or LOB storage characteristics will be logged

in the redo log file (LOGGING) or not (NOLOGGING).The logging attribute

of the table is independent of that of its indexes.

This

attribute also specifies whether subsequent direct loader (SQL*Loader)

and direct-path INSERT operations against the table, partition, or LOB

storage are logged (LOGGING) or not logged (NOLOGGING).

6. table_compression

The

table_compression clause is valid only for heap-organized tables. Use

this clause to instruct the database whether to compress data segments

to reduce disk use. The COMPRESS keyword enables table compression.The NOCOMPRESS keyword disables table compression. NOCOMPRESS is the default.

(1)When you enable table compression by specifying either COMPRESS or COMPRESS BASIC, you enablebasic table compression.

Oracle Database attempts to compress data during direct-path INSERT

operations when it is productive to do so. The original import utility

(imp) does not support direct-path INSERT, and therefore cannot import

data in a compressed format.

Tables with COMPRESS or COMPRESS BASICuse a PCTFREE value of 0 to maximize compression,unless you explicitly set a value for PCTFREE in the physical_attributes_clause.

In

earlier releases, this type of compression was called DSS table

compression and was enabled using COMPRESS FOR DIRECT_LOAD OPERATIONS.

This syntax has been deprecated.

(2)When you enable table compression by specifying COMPRESS FOR OLTP, you enableOLTP table compression.

Oracle Database compresses data during all DML operations on the table.

This form of compression is recommended for OLTP environments.

Tables with COMPRESS FOR OLTP or NOCOMPRESSuse the PCTFREE default value of 10,

to maximize compress while still allowing for some future DML changes

to the data, unless you override this default explicitly.

In earlier releases, OLTP table compression was enabled using COMPRESS FOR ALL OPERATIONS. This syntax has been deprecated.

(3)When you specify COMPRESS FOR QUERY or COMPRESS FOR ARCHIVE,you enable hybrid columnar compression.With hybrid columnar compression, data can be compressed during bulk

load operations. During the load process, data is transformed into a

column-oriented format and then compressed. Oracle Database uses a

compression algorithm appropriate for the level you specify. In general,

the higher the level, the greater the compression ratio. Hybrid

columnar compression can result in higher compression ratios, at a

greater CPU cost. Therefore, this form of compression is recommended for

data that is not frequently updated.

COMPRESS FOR QUERY is useful in data warehousing environments.Valid values are LOW and HIGH, with HIGH providing a higher compression ratio. The default is HIGH.

COMPRESS FOR ARCHIVE uses higher compression ratios than COMPRESS FOR QUERY,and is useful for compressing data that will be stored for long periods

of time. Valid values are LOW and HIGH, with HIGH providing the highest

possible compression ratio. The default is LOW.

Tables

with COMPRESS FOR QUERY or COMPRESS FOR ARCHIVE use a PCTFREE value of 0

to maximize compression, unless you explicitly set a value for PCTFREE

in the physical_attributes_clause. For these tables, PCTFREE has no

effect for blocks loaded using direct-path INSERT. PCTFREE is honored

for blocks loaded using conventional INSERT, and for blocks created as a

result of DML operations on blocks originally loaded using direct-path

INSERT.

7. CACHE | NOCACHE | CACHE READS

Use

the CACHE clauses to indicate how Oracle Database should store blocks

in the buffer cache. If you specify neither CACHE nor NOCACHE, then:

(1).In a CREATE TABLE statement,NOCACHE is the default.

(2).In an ALTER TABLE statement, the existing value is not changed.

CACHE

For

data that is accessed frequently, this clause indicates that the blocks

retrieved for this table are placed at the most recently used end of

the least recently used (LRU) list in the buffer cache when a full table

scan is performed.This attribute is useful for small lookup tables.

As

a parameter in the LOB_storage_clause, CACHE specifies that the

database places LOB data values in the buffer cache for faster access.

The database evaluates this parameter in conjunction with the

logging_clause.If you omit this clause, then the default value for both BasicFile and SecureFile LOBs is NOCACHE LOGGING.

Restriction on CACHE

You cannot specify CACHE for an index-organized table. However, index-organized tables implicitly provide CACHE behavior.

NOCACHE

For

data that is not accessed frequently, this clause indicates that the

blocks retrieved for this table are placed at the least recently used

end of the LRU list in the buffer cache when a full table scan is

performed.NOCACHE is the default for LOB storage.

As

a parameter in the LOB_storage_clause, NOCACHE specifies that the LOB

values are not brought into the buffer cache. NOCACHE is the default for

LOB storage.

Restriction on NOCACHE

You cannot specify NOCACHE for an index-organized table.

CACHE READS

CACHE READS applies only to LOB storage. It specifies that LOB values are brought into the buffer cache only during read operations but not during write operations.

8.parallel_clause

The

parallel_clause lets you parallelize creation of the table and set the

default degree of parallelism for queries and the DML INSERT, UPDATE,

DELETE, and MERGE after table creation.

Note:

The

syntax of the parallel_clause supersedes syntax appearing in earlier

releases of Oracle. Superseded syntax is still supported for backward

compatibility but may result in slightly different behavior from that

documented.

NOPARALLEL

Specify NOPARALLEL for serial execution.This is the default.

PARALLEL

Specify

PARALLEL if you want Oracle to select a degree of parallelism equal to

the number of CPUs available on all participating instances times the

value of the PARALLEL_THREADS_PER_CPU initialization parameter.

PARALLEL integerSpecification ofinteger indicates the degree of parallelism,which is the number of parallel threads used in the parallel operation.

Each parallel thread may use one or two parallel execution servers.

Normally Oracle calculates the optimum degree of parallelism, so it is

not necessary for you to specify integer.

可通过数据字典表DBA_TABLES、ALL_TABLES、USER_TABLES查看参数设置情况，如：

Select table_name,initial_extent,next_extent,min_extents,max_extents,pct_increase from user_tables;