CREATE TABLESPACE

Purpose

Use the CREATE TABLESPACE statement to create a tablespace, which is an allocation of space in the database that can contain persistent schema objects.

When you create a tablespace, it is initially a read/write tablespace. You can subsequently use the ALTER TABLESPACE statement to take the tablespace offline or online, add datafiles to it, or make it a read-only tablespace.

You can also drop a tablespace from the database with the DROP TABLESPACE statement.

You can use the CREATE TEMPORARY TABLESPACE statement to create tablespaces that contain schema objects only for the duration of a session.

Note: To use objects in a tablespace other than the SYSTEM tablespace: If you are running the database in rollback undo mode, at least one rollback segment (other than the SYSTEM rollback segment) must be online. If you are running the database in Automatic Undo Management mode, at least one UNDO tablespace must be online.

See Also: Oracle9i Database Concepts for information on tablespaces ALTER TABLESPACE for information on modifying tablespaces DROP TABLESPACE for information on dropping tablespaces CREATE TEMPORARY TABLESPACE

Additional Topics

• Prerequisites

• Syntax

• Keywords and Parameters

• Examples

Prerequisites

You must have CREATE TABLESPACE system privilege.

Before you can create a tablespace, you must create a database to contain it, and the database must be open.

See Also: CREATE DATABASE

Syntax

create_tablespace::=

Text description of create_tablespace

filespec: See filespec.

autoextend_clause::=

Text description of autoextend_clause

maxsize_clause::=

Text description of maxsize_clause

storage_clause: See storage_clause.

extent_management_clause::=

Text description of extent_management_clause

segment_management_clause::=

Text description of segment_management_clause

Keywords and Parameters

UNDO

Specify UNDO to create an undo tablespace. When you run the database in Automatic Undo Management mode, Oracle manages undo space using the undo tablespace instead of rollback segments. This clause is useful if you are now running in Automatic Undo Management mode but your database was not created in Automatic Undo Management mode.

Oracle always assigns an undo tablespace when you start up the database in Automatic Undo Management mode. If no undo tablespace has been assigned to this instance, Oracle will use the SYSTEM rollback segment. You can avoid this by creating an undo tablespace, which Oracle will implicitly assign to the instance if no other undo tablespace is currently assigned.

Restrictions:

• You cannot create database objects in this tablespace. It is reserved for system-managed undo data.

• The only clauses you can specify for an undo tablespace are the DATAFILE clause and the extent_management_clause to specify local extent management. (You cannot specify dictionary extent management using the extent_management_clause.) All undo tablespaces are created permanent, read/write, and in logging mode. Values for MINIMUM EXTENT and DEFAULT STORAGE are system generated.

  See Also: Oracle9i Database Administrator's Guide for information on Automatic Undo Management and undo tablespaces CREATE DATABASE for information on creating an undo tablespace implicitly or explicitly during database creation ALTER TABLESPACE for information about altering undo tablespaces DROP TABLESPACE for information about dropping undo tablespaces Oracle9i Database Reference for information on opening a database instance in Automatic Undo Management mode using the UNDO_MANAGEMENT parameter

tablespace

Specify the name of the tablespace to be created.

DATAFILE filespec

Specify the datafile or files to make up the tablespace.

Note: For operating systems that support raw devices, the REUSE keyword of filespec has no meaning when specifying a raw device as a datafile. Such a CREATE TABLESPACE statement will succeed whether or not you specify REUSE.

The DATAFILE clause is optional only if the DB_CREATE_FILE_DEST initialization parameter is set. In this case, Oracle creates a system-named 100MB file in the default file destination specified in the parameter.

See Also: filespec for more information on specifying datafiles

autoextend_clause

Use the autoextend_clause to enable or disable the automatic extension of a new datafile or tempfile. If you do not specify this clause, these files are not automatically extended.

ON

Specify ON to enable autoextend.

OFF

Specify OFF to turn off autoextend if is turned on.

Note: When you turn off autoextend, the values of NEXT and MAXSIZE are set to zero. If you turn autoextend back on in a subsequent statement, you must reset these values.

NEXT

Use the NEXT clause to specify the size in bytes of the next increment of disk space to be allocated automatically when more extents are required. Use K or M to specify this size in kilobytes or megabytes. The default is the size of one data block.

MAXSIZE

Use the MAXSIZE clause to specify the maximum disk space allowed for automatic extension of the datafile.

UNLIMITED

Use the UNLIMITED clause if you do not want to limit the disk space that Oracle can allocate to the datafile or tempfile.

MINIMUM EXTENT Clause

Specify the minimum size of an extent in the tablespace. This clause lets you control free space fragmentation in the tablespace by ensuring that every used or free extent size in a tablespace is at least as large as, and is a multiple of, integer.

Note: This clause is not relevant for a dictionary-managed temporary tablespace.

See Also: Oracle9i Database Concepts for more information about using MINIMUM EXTENT to control fragmentation

BLOCKSIZE Clause

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

Restriction: You cannot specify nonstandard block sizes for a temporary tablespace (that is, if you also specify TEMPORARY) or if you intend to assign this tablespace as the temporary tablespace for any users.

See Also: Oracle9i Database Administrator's Guide for information on allowing multiple block sizes in the buffer cache, and for restrictions on using multiple block sizes in partitioned objects

LOGGING | NOLOGGING

Specify the default logging attributes of all tables, indexes, and partitions within the tablespace. LOGGING is the default.

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

Only the following operations support the NOLOGGING mode:

• DML: direct-path INSERT (serial or parallel), Direct Loader (SQL*Loader)

• DDL: CREATE TABLE ... AS SELECT, CREATE INDEX, ALTER INDEX ... REBUILD, ALTER INDEX ... REBUILD PARTITION, ALTER INDEX ... SPLIT PARTITION, ALTER TABLE ... SPLIT PARTITION, and ALTER TABLE ... MOVE PARTITION

In NOLOGGING mode, data is modified with minimal logging (to mark new extents INVALID and to record dictionary changes). When applied during media recovery, the extent invalidation records mark a range of blocks as logically corrupt, because the redo data is not logged. Therefore, if you cannot afford to lose the object, you should take a backup after the NOLOGGING operation.

DEFAULT storage_clause

Specify the default storage parameters for all objects created in the tablespace.

For a dictionary-managed temporary tablespace, Oracle considers only the NEXT parameter of the storage_clause.

See Also: for information on storage parameters

ONLINE | OFFLINE Clauses

ONLINE

Specify ONLINE to make the tablespace available immediately after creation to users who have been granted access to the tablespace. This is the default.

OFFLINE

Specify OFFLINE to make the tablespace unavailable immediately after creation.

The data dictionary view DBA_TABLESPACES indicates whether each tablespace is online or offline.

PERMANENT | TEMPORARY Clauses

PERMANENT

Specify PERMANENT if the tablespace will be used to hold permanent objects. This is the default.

TEMPORARY

Specify TEMPORARY if the tablespace will be used only to hold temporary objects, for example, segments used by implicit sorts to handle ORDER BY clauses.

Restriction: If you specify TEMPORARY, you cannot specify EXTENT MANAGEMENT LOCAL or the BLOCKSIZE clause.

extent_management_clause

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

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

• Specify LOCAL if you want the tablespace to be locally managed. Locally managed tablespaces have some part of the tablespace set aside for a bitmap. This is the default.
  • AUTOALLOCATE specifies that the tablespace is system managed. Users cannot specify an extent size. This is the default if the COMPATIBLE initialization parameter is set to 9.0.0 or higher.

  • UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes. Use K or M to specify the extent size in kilobytes or megabytes. The default SIZE is 1 megabyte.

• Specify DICTIONARY if you want the tablespace to be managed using dictionary tables. This is the default if the COMPATIBLE initialization parameter is set less than 9.0.0.

If you do not specify the extent_management_clause, Oracle interprets the COMPATIBLE setting, the MINIMUM EXTENT clause and the DEFAULT storage_clause to determine extent management:

• If the COMPATIBLE initialization parameter is less than 9.0.0, Oracle creates a dictionary managed tablespace. If COMPATIBLE = 9.0.0 or higher:

• If you do not specify the DEFAULT storage_clause at all, Oracle creates a locally managed autoallocated tablespace.

• If you did specify the DEFAULT storage_clause:
  • If you specified the MINIMUM EXTENT clause, Oracle evaluates whether the values of MINIMUM EXTENT, INITIAL, and NEXT are equal and the value of PCT_INCREASE is 0. If so, Oracle creates a locally managed uniform tablespace with extent size = INITIAL. If the MINIMUM EXTENT, INITIAL, and NEXT parameters are not equal, or if PCT_INCREASE is not 0, Oracle ignores any extent storage parameters you may specify and creates a locally managed, autoallocated tablespace.

  • If you did not specify MINIMUM EXTENT clause, Oracle evaluates only whether the storage values of INITIAL and NEXT are equal and PCT_INCREASE is 0. If so, the tablespace is locally managed and uniform. Otherwise, the tablespace is locally managed and autoallocated.

    See Also: Oracle9i Database Concepts for a discussion of locally managed tablespaces

Restrictions:

• A permanent locally managed tablespace can contain only permanent objects. If you need a locally managed tablespace to store temporary objects (for example, if you will assign it as a user's temporary tablespace, use the CREATE TEMPORARY TABLESPACE statement.

• If you specify LOCAL, you cannot specify DEFAULT storage_clause, MINIMUM EXTENT, or TEMPORARY.

  See Also: Oracle9i Database Migration for information on changing extent management by migrating tablespaces

segment_management_clause

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

MANUAL

Specify MANUAL if you want Oracle to manage the free space of segments in the tablespace using free lists.

AUTO

Specify AUTO if you want Oracle to manage the free space of segments in the tablespace using a bitmap. If you specify AUTO, Oracle ignores any specification for FREELIST and FREELIST GROUPS in subsequent storage specifications for objects in this tablespace. This setting is called 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, then: If you set extent management to LOCAL UNIFORM, you must ensure that each extent contains at least 5 database blocks, given the database block size. If you set extent management to LOCAL AUTOALLOCATE, and if the database block size is 16K or greater, Oracle manages segment space management by creating extents with a minimum size of 1M.

Restrictions on AUTO:

• You can specify this clause only for permanent, locally managed tablespace.

• You cannot specify this clause for the SYSTEM tablespace.

• You cannot store LOBs in AUTO segment-managed tablespaces.

  See Also: Oracle9i Database Administrator's Guide for information on automatic segment space management and when to use it Oracle9i Database Reference for information on the data dictionary views

Examples

The following example creates a 10 MB undo tablespace undots1 with datafile undotbs_1a.f:

CREATE UNDO TABLESPACE undots1
   DATAFILE 'undotbs_1a.f'
   SIZE 10M AUTOEXTEND ON;

DEFAULT Storage Example

This statement creates a tablespace named tabspace_2 with one datafile:

CREATE TABLESPACE tbs_1 
   DATAFILE 'tabspace_file2.dat' SIZE 20M 
   DEFAULT STORAGE (INITIAL 10K NEXT 50K 
                    MINEXTENTS 1 MAXEXTENTS 999) 
   ONLINE; 

AUTOEXTEND Example

This statement creates a tablespace named tabspace_5 with one datafile. When more space is required, 500 kilobyte extents will be added up to a maximum size of 10 megabytes:

CREATE TABLESPACE tbs_2 
   DATAFILE 'diskb:tabspace_file5.dat' SIZE 500K REUSE
   AUTOEXTEND ON NEXT 500K MAXSIZE 10M;

MINIMUM EXTENT Example

This statement creates tablespace tabspace_3 with one datafile and allocates every extent as a multiple of 64K:

CREATE TABLESPACE tbs_3 
   DATAFILE 'tabspace_file3.dbf' SIZE 2M
   MINIMUM EXTENT 64K
   DEFAULT STORAGE (INITIAL 128K NEXT 128K)
   LOGGING;

Locally Managed Example

In the following statement, we assume that the database block size is 2K.

CREATE TABLESPACE tbs_4 DATAFILE 'file_1.f' SIZE 10M
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

This statement creates a locally managed tablespace in which every extent is 128K and each bit in the bit map describes 64 blocks.

Segment Management Example

The following example creates a tablespace with automatic segment space management:

CREATE TABLESPACE auto_seg_ts DATAFILE 'file_2.f' SIZE 1M
   EXTENT MANAGEMENT LOCAL
   SEGMENT SPACE MANAGEMENT AUTO;

Oracle-managed Files Examples

The following example sets the default location for datafile creation and creates a tablespace with a datafile in the default location. The datafile is 100M and is autoextensible with an unlimited maximum size:

ALTER SYSTEM SET DB_CREATE_FILE_DEST = 'u01/oradata/sample';
CREATE TABLESPACE omf_ts1;

The following example creates a tablespace with an Oracle managed datafile of 100M that is not autoextensible:

CREATE TABLESPACE omf_ts2 DATAFILE AUTOEXTEND OFF;