Oracle 表空间创建参数说明

一. 表空间的说明

官网的几个链接：

Logical Storage Structures

http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/logical.htm#CNCPT402

CREATE TABLESPACE

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_7003.htm#SQLRF01403

先看一个表空间的创建SQL:

CREATE TABLESPACE SYSAUX DATAFILE

'/u01/app/oracle/oradata/orcl/sysaux01.dbf' SIZE 250M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON;

结合这个例子，对几个参数做下说明：

1.1 logging_clause

Specify the default logging attributes of all tables, indexes, materialized views, materialized view logs, and partitions within the tablespace. LOGGING is the default. This clause is not valid for a temporary or undo tablespace.

The tablespace-level logging attribute can be overridden by logging specifications at the table, index, materialized view, materialized view log, and partition levels.

--指定表，视图，索引等的loging 属性。该属性不行应用与undo 和 temporary 表空间。在表空间级别设置的logging 属性可以被表等对象自身的属性覆盖。

1.2 permanent_tablespace_clause

Use the following clauses to create a permanent tablespace. (Some of these clauses are also used to create a temporary or undo tablespace.)

tablespace

Specify the name of the tablespace to be created.

Note on the SYSAUX Tablespace SYSAUX is a required auxiliary system tablespace. You must use the CREATE TABLESPACE statement to create the SYSAUX tablespace if you are upgrading from a release prior to Oracle Database 11g. You must have the SYSDBA system privilege to specify this clause, and you must have opened the database in MIGRATE mode.

You must specify EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO for the SYSAUX tablespace. The DATAFILE clause is optional only if you have enabled Oracle Managed Files.

See "DATAFILE | TEMPFILE Clause" for the behavior of the DATAFILE clause.

Take care to allocate sufficient space for the SYSAUX tablespace. For guidelines on creating this tablespace, refer to Oracle Database Upgrade Guide.

Restrictions on the SYSAUX Tablespace You cannot specify OFFLINE or TEMPORARY for the SYSAUX tablespace.

--该选项主要用于指定指定表空间的类型，permanent 表示永久的，如果是其他类型，则写temporary 或者undo。如果是创建SYSAUX 表空间，则必须指定extent managent 类型和 segment space management 类型。

1.3 extent_management_clause

The extent_management_clause lets you specify how the extents of the tablespace will be managed.

Note:

After you have specified extent management with this clause, you can change extent management only by migrating the tablespace.

（1）. AUTOALLOCATE specifies that the tablespace is system managed. Users cannot specify an extent size. You cannot specify AUTOALLOCATE for a temporary tablespace.

（2）. UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes.The default SIZE is 1 megabyte. All extents of temporary tablespaces are of uniform size, so this keyword is optional for a temporary tablespace. However, you must specify UNIFORM in order to specify SIZE. You cannot specify UNIFORM for an undo tablespace.

If you do not specify AUTOALLOCATE or UNIFORM, then the default is UNIFORM for temporary tablespaces and AUTOALLOCATE for all other types of tablespaces.

If you do not specify the extent_management_clause, then Oracle Database interprets the MINIMUM EXTENT clause and the DEFAULT storage_clause to determine extent management.

Note:

The DICTIONARY keyword is deprecated. It is still supported for backward compatibility. However, Oracle recommends that you create locally managed tablespaces. Locally managed tablespaces are much more efficiently managed than dictionary-managed tablespaces. The creation of new dictionary-managed tablespaces is scheduled for desupport.

根据表空间中区的管理方式不周，表空间可以分为数据字典管理（Dictionary managed）与本地化管理（Local Managed）类型。

在数据字典管理表空间中，区大小由参数：

initial, next, minextents, maxextents, pctincrease 决定。

在本地化管理中，区大小设置方式分为uniform 及autoallocate 两种类型。

Uniform：区的大小相同。如果设置了uniform 则pctincrease 自动为0。

Autoallocate：区大小oracle自动分配。

Oracle9i之前的，通常是数据字典管理。Oracle9i及以后，通常使用本地化管理表空间。

1.4 BLOCKSIZE Clause

Use the BLOCKSIZE clause to specify a nonstandard block size for the tablespace. In order to specify this clause, the DB_CACHE_SIZE and at least one DB_nK_CACHE_SIZE parameter must be set, and the integer you specify in this clause must correspond with the setting of one DB_nK_CACHE_SIZE parameter setting.

Restriction on BLOCKSIZE You cannot specify nonstandard block sizes for a temporary tablespace or if you intend to assign this tablespace as the temporary tablespace for any users.

1.5 segment_management_clause

The segment_management_clause is relevant only for permanent, locally managed tablespaces. It lets you specify whether Oracle Database should track the used and free space in the segments in the tablespace using free lists or bitmaps. This clause is not valid for a temporary tablespace.

（1）AUTO

Specify AUTO if you want the database to manage the free space of segments in the tablespace using a bitmap. If you specify AUTO, then the database ignores any specification for PCTUSED, FREELIST, and FREELIST GROUPS in subsequent storage specifications for objects in this tablespace. This setting is called automatic segment-space management and is the default.

（2）MANUAL

Specify MANUAL if you want the database to manage the free space of segments in the tablespace using free lists. Oracle strongly recommends that you do not use this setting and that you create tablespaces with automatic segment-space management.

To determine the segment management of an existing tablespace, query the SEGMENT_SPACE_MANAGEMENT column of the DBA_TABLESPACES or USER_TABLESPACES data dictionary view.

Notes:

If you specify AUTO segment management, then:

（1）. If you set extent management to LOCAL UNIFORM, then you must ensure that each extent contains at least 5 database blocks.

（2）. If you set extent management to LOCAL AUTOALLOCATE, and if the database block size is 16K or greater, then Oracle manages segment space by creating extents with a minimum size of 5 blocks rounded up to 64K.

Restrictions on Automatic Segment-Space Management This clause is subject to the following restrictions:

（1）. You can specify this clause only for a permanent, locally managed tablespace.

（2）. You cannot specify this clause for the SYSTEM tablespace.

1.6 flashback_mode_clause

Use this clause in conjunction with the ALTER DATABASE FLASHBACK clause to specify whether the tablespace can participate in FLASHBACK DATABASE operations. This clause is useful if you have the database in FLASHBACK mode but you do not want Oracle Database to maintain Flashback log data for this tablespace.

This clause is not valid for temporary or undo tablespaces.

（1）FLASHBACK ON

Specify FLASHBACK ON to put the tablespace in FLASHBACK mode. Oracle Database will save Flashback log data for this tablespace and the tablespace can participate in a FLASHBACK DATABASE operation. If you omit the flashback_mode_clause, then FLASHBACK ON is the default.

（2）FLASHBACK OFF

Specify FLASHBACK OFF to take the tablespace out of FLASHBACK mode. Oracle Database will not save any Flashback log data for this tablespace. You must take the data files in this tablespace offline or drop them prior to any subsequent FLASHBACK DATABASE operation. Alternatively, you can take the entire tablespace offline. In either case, the database does not drop existing Flashback logs.

Note:

The FLASHBACK mode of a tablespace is independent of the FLASHBACK mode of an individual table.

二. 其他有关表空间的操作

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

http://blog.csdn.net/tianlesoftware/archive/2009/12/13/4962476.aspx

Oracle 表空间基本操作

http://blog.csdn.net/tianlesoftware/archive/2009/10/16/4681973.aspx

Oracle Temp 临时表空间

http://blog.csdn.net/tianlesoftware/archive/2009/10/19/4697417.aspx

Oracle undo 表空间管理

http://blog.csdn.net/tianlesoftware/archive/2010/06/23/5689558.aspx

RAC实例表空间维护