CREATE TABLESPACE

Syntax

filespec: See "filespec".

autoextend_clause::=

maxsize_clause::=

storage_clause: See "storage_clause".

extent_management_clause::=

Purpose

To create a tablespace. A tablespace is an allocation of space in the database that can contain schema objects. For information on tablespaces, see Oracle8i Concepts.

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. See "ALTER TABLESPACE".

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

Prerequisites

You must have CREATE TABLESPACE system privilege. Also, the SYSTEM tablespace must contain at least two rollback segments including the SYSTEM rollback segment.

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

Keywords and Parameters

tablespace	is the name of the tablespace to be created.
DATAFILE filespec	specifies the datafile or files to make up the tablespace. See "filespec".
	Note: For operating systems that support raw devices, the filespec REUSE keyword has no meaning when specifying a raw device as a datafile. Such a CREATE TABLESPACE statement will succeed whether or not you specify REUSE.
autoextend_clause	enables or disables the automatic extension of the datafile.
	OFF	disables autoextend if it is turned on. NEXT and MAXSIZE are set to zero. Values for NEXT and MAXSIZE must be respecified in further ALTER TABLESPACE AUTOEXTEND statements.
	ON	enables autoextend.
	NEXT	specifies the disk space to allocate to the datafile when more extents are required.
	maxsize_clause	specifies the maximum disk space allowed for allocation to the datafile.
	UNLIMITED	sets no limit on allocating disk space to the datafile.
MINIMUM EXTENT integer	controls 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. See also: Oracle8i Concepts for more information about using MINIMUM EXTENT to control fragmentation.
	Note: This clause is not relevant for a dictionary-managed temporary tablespace.
LOGGING| NOLOGGING	specifies 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-load 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	specifies 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. For information on storage parameters, see the "storage_clause".
ONLINE	makes the tablespace available immediately after creation to users who have been granted access to the tablespace. This is the default.
OFFLINE	makes the tablespace unavailable immediately after creation.
	The data dictionary view DBA_TABLESPACES indicates whether each tablespace is online or offline.
PERMANENT	specifies that the tablespace will be used to hold permanent objects. This is the default.
TEMPORARY	specifies that 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.
extent_management_clause	specifies how the extents of the tablespace will be managed.
	DICTIONARY	specifies that the tablespace is managed using dictionary tables. This is the default.
	LOCAL	specifies that tablespace is locally managed. Locally managed tablespaces have some part of the tablespace set aside for a bitmap. For a discussion of locally managed tablespaces, see Oracle8i Concepts. AUTOALLOCATE specifies that the tablespace is system managed. Users cannot specify an extent size. 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.
		If you do not specify either AUTOALLOCATE or UNIFORM, then AUTOALLOCATE is the default. Restriction: If you specify LOCAL, you cannot specify DEFAULT storage_clause, MINIMUM EXTENT, or TEMPORARY.

Examples

DEFAULT Storage Example

This statement creates a tablespace named TABSPACE_2 with one datafile:

CREATE TABLESPACE tabspace_2 
   DATAFILE 'diska: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_3 with one datafile. When more space is required, 50 kilobyte extents will be added up to a maximum size of 10 megabytes:

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

MINIMUM EXTENT Example

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

CREATE TABLESPACE tabspace_3 
   DATAFILE 'tabspace_file5.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_1 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.