Oracle7 Server SQL Reference

CREATE TABLESPACE

Purpose

To create a tablespace. A tablespace is an allocation of space in the database that can contain objects.

Prerequisites

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

Syntax

Keywords and Parameters

tablespace

is the name of the tablespace to be created.

DATAFILE

specifies the data file or files to comprise the tablespace. See the syntax description of filespec .

AUTOEXTEND

enables or disables the automatic extension of datafile.

OFF 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 commands.

ON enable autoextend.

NEXT disk space to allocate to the datafile when more extents are required.

MAXSIZE maximum disk space allowed for allocation to the datafile.

UNLIMITED set no limit on allocating disk space to the datafile.

DEFAULT STORAGE

specifies the default storage parameters for all objects created in the tablespace. 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.

OFFLINE

makes the tablespace unavailable immediately after creation.

If you omit both the ONLINE and OFFLINE options, Oracle7 creates the tablespace online by default. 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 only be used to hold temporary objects. For example, segments used by implicit sorts to handle ORDER BY clauses.

Usage Notes

A tablespace is an allocation of space in the database that can contain any of the following segments:

data segments

index segments

rollback segments

temporary segments

All databases have at least one tablespace, SYSTEM , which Oracle7 creates automatically when you create the database.

When you create a tablespace, it is initially a read-write tablespace. After creating the tablespace, you can subsequently use the ALTER TABLESPACE command to take it offline or online, add data files to it, or make it a read-only tablespace.

Many schema objects have associated segments that occupy space in the database. These objects are located in tablespaces. The user creating such an object can optionally specify the tablespace to contain the object. The owner of the schema containing the object must have space quota on the object's tablespace. You can assign space quota on a tablespace to a user with the QUOTA clause of the CREATE USER or ALTER USER commands.

Warning: For operating systems that support raw devices, be aware that the STORAGE clause REUSE keyword has no meaning when specifying a raw device as a datafile in a CREATE TABLESPACE command; such a command will always succeed even if REUSE is not specified.

Example I

This command 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 
	                 PCTINCREASE 10) 
	ONLINE

Example II

This command 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: