Using CREATE TABLESPACE statement in Oracle

This article is written about how to use the Oracle CREATE TABLESPACE statement with syntax and examples.

Description

The CREATE TABLESPACE announcement is used to allocate space in the Oracle database where schema objects are stored.

The CREATE TABLESPACE declaration can be used to create the three kinds of tablespaces:

Permanent Tablespace Temporary Tablespace Undo Tablespace

We will take a appear at all 3 sorts of tablespaces.

#1 – PERMANENT TABLESPACE

A everlasting tablespace incorporates power schema objects that are saved in records files.

Syntax

The syntax for the CREATE TABLESPACE declaration when creating a permanent tablespace is:

CREATE
  [ SMALLFILE | BIGFILE ]
  TABLESPACE tablespace_name
  { DATAFILE { [ 'filename' | 'ASM_filename' ]
               [ SIZE integer [ K | M | G | T | P | E ] ]
               [ REUSE ]
               [ AUTOEXTEND
                   { OFF
                   | ON [ NEXT integer [ K | M | G | T | P | E ] ]
                   [ MAXSIZE { UNLIMITED | integer [ K | M | G | T | P | E ] } ]
                   }
               ]
             | [ 'filename | ASM_filename'
             | ('filename | ASM_filename'
                 [, 'filename | ASM_filename' ] )
             ]
             [ SIZE integer [ K | M | G | T | P | E ] ]
             [ REUSE ]
             }
     { MINIMUM EXTENT integer [ K | M | G | T | P | E ]
     | BLOCKSIZE integer [ K ]
     | { LOGGING | NOLOGGING }
     | FORCE LOGGING
     | DEFAULT [ { COMPRESS | NOCOMPRESS } ]
   storage_clause
     | { ONLINE | OFFLINE }
     | EXTENT MANAGEMENT
        { LOCAL
           [ AUTOALLOCATE
           | UNIFORM
              [ SIZE integer [ K | M | G | T | P | E ] ]
           ]
        | DICTIONARY
        }
     | SEGMENT SPACE MANAGEMENT { AUTO | MANUAL }
     | FLASHBACK { ON | OFF }
         [ MINIMUM EXTENT integer [ K | M | G | T | P | E ]
         | BLOCKSIZE integer [ K ]
         | { LOGGING | NOLOGGING }
         | FORCE LOGGING
         | DEFAULT [ { COMPRESS | NOCOMPRESS } ]
         storage_clause
         | { ONLINE | OFFLINE }
         | EXTENT MANAGEMENT
              { LOCAL
                [ AUTOALLOCATE | UNIFORM [ SIZE integer [ K | M | G | T | P | E ] ] ]
                | DICTIONARY
              }
         | SEGMENT SPACE MANAGEMENT { AUTO | MANUAL }
         | FLASHBACK { ON | OFF }
         ]
     }

SMALLFILE A tablespace that consists of 1,022 statistics or temp documents (each file can be up to 4 million blocks in size). This is the most common tablespace dimension to create. BIGFILE A tablespace that carries solely one data or temp file (this file can be up to four million blocks in size).

TIP: If you omit the SMALLFILE or BIGFILE option, the Oracle database will use the default tablespace type.

tablespace_name The identify of the tablespace to create. storage_clause The syntax for the storage_clause is:

STORAGE
   ({ INITIAL integer [ K | M | G | T | P | E ]
    | NEXT integer [ K | M | G | T | P | E ]
    | MINEXTENTS integer
    | MAXEXTENTS { integer | UNLIMITED }
    | PCTINCREASE integer
    | FREELISTS integer
    | FREELIST GROUPS integer
    | OPTIMAL [ integer [ K | M | G | T | P | E ] | NULL ]
    | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
    }
       [ INITIAL integer [ K | M | G | T | P | E ]
       | NEXT integer [ K | M | G | T | P | E ]
       | MINEXTENTS integer
       | MAXEXTENTS { integer | UNLIMITED }
       | PCTINCREASE integer
       | FREELISTS integer
       | FREELIST GROUPS integer
       | OPTIMAL [ integer [ K | M | G | T | P | E ] | NULL ]
       | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
       ]
   )

Example – PERMANENT TABLESPACE

The following is a CREATE TABLESPACE announcement that creates a simple everlasting tablespace:

CREATE TABLESPACE tbs_perm_01
  DATAFILE 'tbs_perm_01.dat' 
    SIZE 20M
  ONLINE;

This CREATE TABLESPACE announcement creates a everlasting tablespace known as tbs_perm_01 that has one data file called tbs_perm_01.dat.

The following is a CREATE TABLESPACE statement that creates a everlasting tablespace that will lengthen when greater house is required:

CREATE TABLESPACE tbs_perm_02
  DATAFILE 'tbs_perm_02.dat' 
    SIZE 10M
    REUSE
    AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

This CREATE TABLESPACE announcement creates a everlasting tablespace called tbs_perm_02 that has one facts file known as tbs_perm_02.dat. When more space is required, 10M extents will mechanically be introduced till 200MB is reached.

The following is a CREATE TABLESPACE declaration that creates a BIGFILE everlasting tablespace that will prolong when extra space is required:

CREATE BIGFILE TABLESPACE tbs_perm_03
  DATAFILE 'tbs_perm_03.dat'
    SIZE 10M
    AUTOEXTEND ON;

This CREATE TABLESPACE announcement creates a BIGFILE everlasting tablespace referred to as tbs_perm_03 that has one records file called tbs_perm_03.dat.

#2 – TEMPORARY TABLESPACE

A temporary tablespace consists of schema objects that are stored in temp files that exist all through a session.

Syntax

The syntax for the CREATE TABLESPACE declaration when developing a transient tablespace is:

CREATE
  [ SMALLFILE | BIGFILE ]
  TEMPORARY TABLESPACE tablespace_name
    [ TEMPFILE { [ 'filename' | 'ASM_filename' ]
                 [ SIZE integer [ K | M | G | T | P | E ] ]
                 [ REUSE ]
                 [ AUTOEXTEND
                     { OFF
                     | ON [ NEXT integer [ K | M | G | T | P | E ] ]
                     [ MAXSIZE { UNLIMITED | integer [ K | M | G | T | P | E ] } ]
                     }
                 ]
               | [ 'filename | ASM_filename'
               | ('filename | ASM_filename'
                   [, 'filename | ASM_filename' ] )
               ]
               [ SIZE integer [ K | M | G | T | P | E ] ]
               [ REUSE ]
               }
    [ TABLESPACE GROUP { tablespace_group_name | '' } ]
    [ EXTENT MANAGEMENT
       { LOCAL
          [ AUTOALLOCATE | UNIFORM [ SIZE integer [ K | M | G | T | P | E ] ] ]
       | DICTIONARY
       } ]

SMALLFILE A tablespace that consists of 1,022 data or temp documents (each file can be up to 4 million blocks in size). This is the most frequent tablespace dimension to create. BIGFILE A tablespace that contains only one statistics or temp file (this file can be up to four million blocks in size).

TIP: If you pass over the SMALLFILE or BIGFILE option, the Oracle database will use the default tablespace type.

tablespace_name The identify of the tablespace to create.

Example – TEMPORARY TABLESPACE

The following is a CREATE TABLESPACE statement that creates a brief tablespace:

CREATE TEMPORARY TABLESPACE tbs_temp_01
  TEMPFILE 'tbs_temp_01.dbf'
    SIZE 5M
    AUTOEXTEND ON;

This CREATE TABLESPACE declaration creates a temporary tablespace referred to as tbs_temp_01 that has one temp file known as tbs_temp_01.dbf.

#3 – UNDO TABLESPACE

An undo tablespace is created to control undo facts if the Oracle database is being run in computerized undo management mode.

Syntax

The syntax for the CREATE TABLESPACE announcement when creating an undo tablespace is:

CREATE
  [ SMALLFILE | BIGFILE ]
  UNDO TABLESPACE tablespace_name
    [ DATAFILE { [ 'filename' | 'ASM_filename' ]
                 [ SIZE integer [ K | M | G | T | P | E ] ]
                 [ REUSE ]
                 [ AUTOEXTEND
                     { OFF
                     | ON [ NEXT integer [ K | M | G | T | P | E ] ]
                     [ MAXSIZE { UNLIMITED | integer [ K | M | G | T | P | E ] } ]
                     }
                 ]
               | [ 'filename | ASM_filename'
               | ('filename | ASM_filename'
                   [, 'filename | ASM_filename' ] )
               ]
               [ SIZE integer [ K | M | G | T | P | E ] ]
               [ REUSE ]
               }
    [ EXTENT MANAGEMENT
       { LOCAL
          [ AUTOALLOCATE | UNIFORM [ SIZE integer [ K | M | G | T | P | E ] ] ]
       | DICTIONARY
       } ]
    [ RETENTION { GUARANTEE | NOGUARANTEE } ]

SMALLFILE A tablespace that carries 1,022 records or temp archives (each file can be up to 4 million blocks in size). This is the most common tablespace dimension to create. BIGFILE A tablespace that incorporates solely one facts or temp file (this file can be up to four million blocks in size).

TIP: If you pass over the SMALLFILE or BIGFILE option, the Oracle database will use the default tablespace type.

tablespace_name The title of the tablespace to create.

Example – UNDO TABLESPACE

The following is a CREATE TABLESPACE announcement that creates an undo tablespace:

CREATE UNDO TABLESPACE tbs_undo_01
  DATAFILE 'tbs_undo_01.f'
    SIZE 5M 
    AUTOEXTEND ON
  RETENTION GUARANTEE;

This CREATE TABLESPACE statement creates an undo tablespace referred to as tbs_undo_01 that is 5MB in dimension and has one statistics file known as tbs_undo_01.f.