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.

See Also: Oracle8i 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. 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 Also: CREATE DATABASE

Syntax

filespec: See filespec.

autoextend_clause::=

maxsize_clause::=

storage_clause: See storage_clause.

extent_management_clause::=

Keywords and Parameters

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.

See Also: filespec

autoextend_clause

Use the autoextend_clause to enable or disable the automatic extension of the datafile.

OFF	Specify OFF to disable 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	Specify ON to enable autoextend.
NEXT integer	Specify the disk space to allocate to the datafile when more extents are required.
maxsize_clause	The maxsize_clause lets you specify the maximum disk space allowed for allocation to the datafile.
	integer: Specify in bytes the maximum disk space allowed for allocation to the tempfile. Use K or M to specify this space in kilobytes or megabytes.
	UNLIMITED: Specify UNLIMITED to set no limit on allocating disk space to the datafile.

MINIMUM EXTENT integer

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: Oracle8i Concepts for more information about using MINIMUM EXTENT to control fragmentation

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

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

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

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.

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.

DICTIONARY	Specify DICTIONARY if you want the tablespace to be managed using dictionary tables. This is the default.
LOCAL	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. 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. See Also: Oracle8i Concepts for a discussion of locally managed tablespaces
	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.
See Also: Oracle8i Migration for information on changing extent management by migrating tablespaces

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.