Oracle7 Server Administrator's Guide

Creating Tablespaces

The steps for creating tablespaces vary by operating system. On most operating systems, you indicate the size and fully specified filenames when creating a creating a new tablespace, or altering a tablespace by adding datafiles. In each situation, Oracle7 automatically allocates and formats the datafiles as specified. However, on some operating systems, you must create the datafiles before installation.

The first tablespace in any database is always the SYSTEM tablespace. Therefore, the first datafiles of any database are automatically allocated for the SYSTEM tablespace during database creation.

You might create a new tablespace for any of the following reasons:

You want to allocate more disk storage space for the associated database, thereby enlarging the database.

You need to create a logical storage structure in which to store a specific type of data separate from other database data.

To increase the total size of the database you can alternatively add a datafile to an existing tablespace, rather than adding a new tablespace.

Note: No data can be inserted into any tablespace until the current instance has acquired at least two rollback segments (including the SYSTEM rollback segment).

To create a new tablespace, use either the Create Tablespace property sheet of Server Manager/GUI, or the SQL command CREATE TABLESPACE. You must have the CREATE TABLESPACE system privilege to create a tablespace.

As an example, let's create the tablespace RB_SEGS (to hold rollback segments for the database), with the following characteristics:

The data of the new tablespace is contained in a single datafile, 50M in size.

The default storage parameters for any segments created in this tablespace are explicitly set.

After the tablespace is created, it is left offline.

The following statement creates the tablespace RB_SEGS:

CREATE TABLESPACE rb_segs

   DATAFILE 'datafilers_1' SIZE 50M

   DEFAULT STORAGE (

      INITIAL 50K

      NEXT 50K

      MINEXTENTS 2

      MAXEXTENTS 50

      PCTINCREASE 0)

   OFFLINE;

If you do not fully specify filenames when creating tablespaces, the corresponding datafiles are created in the current directory of the database server.

See Also: See your operating system-specific Oracle documentation for information about initially creating a tablespace.

For more information about adding a datafile, see "Adding Datafiles to a Tablespace" .

For more information about the CREATE TABLESPACE statement, see the Oracle7 Server SQL Reference.

Creating a Temporary Tablespace

If you wish to improve the concurrency of multiple sort operations, reduce their overhead, or avoid Oracle space management operations altogether, you can create temporary tablespaces.

Within a temporary tablespace, all sort operations for a given instance and tablespace share a single sort segment. Sort segments exist in every instance that performs sort operations within a given tablespace. You cannot store permanent objects in a temporary tablespace. You can view the allocation and deallocation of space in a temporary tablespace sort segment via the V$SORT_SEGMENTS table.

To identify a tablespace as temporary during tablespace creation, issue the following statement:

CREATE TABLESPACE tablespace TEMPORARY

To identify a tablespace as temporary in an existing tablespace, issue the following statement:

ALTER TABLESPACE tablespace TEMPORARY

Note: You can take temporary tablespaces offline. Returning temporary tablespaces online does not affect their temporary status.

See Also: For more information about the CREATE TABLESPACE and ALTER TABLESPACE commands, see the Oracle7 Server SQL Reference.

For more information about V$SORT_SEGMENTS, see the Oracle7 Server Reference.

For more information about Oracle space management, see Oracle7 Server Concepts.