Oracle 表空間 建立引數 說明

一. 表空間的說明

 

先看一個表空間的建立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.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28673746/viewspace-757409/，如需轉載，請註明出處，否則將追究法律責任。