Oracle/PLSQL: CREATE TABLESPACE statement

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

The CREATE TABLESPACE statement can be used to create the 3 kinds of tablespaces:

1. Permanent Tablespace
2. Temporary Tablespace
3. Undo Tablespace

We will take a look at all 3 kinds of tablespaces.

#1 - PERMANENT TABLESPACE

A permanent tablespace contains persistent schema objects that are stored in data files.

The syntax for the CREATE TABLESPACE statement 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 or BIGFILE is optional. It determines the size of the tablespace. If you omit this option, the Oracle database will use the default tablespace type.

• SMALLFILE is a tablespace that contains 1,022 data or temp files (each file can be up to 4 million blocks in size).
• BIGFILE is a tablespace that contains only one data or temp file (this file can be up to 4 million blocks in size).

tablespace_name is the name of the tablespace to create.

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 statement that creates a simple permanent tablespace:

CREATE TABLESPACE tbs_perm_01

  DATAFILE 'tbs_perm_01.dat'

    SIZE 20M

  ONLINE;

This CREATE TABLESPACE statement creates a permanent tablespace called tbs_perm_01 that has one data file called tbs_perm_01.dat.

The following is a CREATE TABLESPACE statement that creates a permanent tablespace that will extend when more space is required:

CREATE TABLESPACE tbs_perm_02

  DATAFILE 'tbs_perm_02.dat'

    SIZE 10M

    REUSE

    AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

This CREATE TABLESPACE statement creates a permanent tablespace called tbs_perm_02 that has one data file called tbs_perm_02.dat. When more space is required, 10M extents will automatically be added until 200MB is reached.

The following is a CREATE TABLESPACE statement that creates a BIGFILE permanent tablespace that will extend when more space is required:

CREATE BIGFILE TABLESPACE tbs_perm_03

  DATAFILE 'tbs_perm_03.dat'

    SIZE 10M

    AUTOEXTEND ON;

This CREATE TABLESPACE statement creates a BIGFILE permanent tablespace called tbs_perm_03 that has one data file called tbs_perm_03.dat.

#2 - TEMPORARY TABLESPACE

A temporary tablespace contains schema objects that are stored in temp files that exist during a session.

The syntax for the CREATE TABLESPACE statement when creating a temporary 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 or BIGFILE is optional. It determines the size of the tablespace. If you omit this option, the Oracle database will use the default tablespace type.

• SMALLFILE is the most common tablespace size to create. It can contain 1,022 data or temp files (each file can be up to 4 million blocks in size).
• BIGFILE is a tablespace that contains only one data or temp file (this file can be up to 4 million blocks in size).

tablespace_name is the name of the tablespace to create.

Example - TEMPORARY TABLESPACE

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

CREATE TEMPORARY TABLESPACE tbs_temp_01

  TEMPFILE 'tbs_temp_01.dbf'

    SIZE 5M

    AUTOEXTEND ON;

This CREATE TABLESPACE statement creates a temporary tablespace called tbs_temp_01 that has one temp file called tbs_temp_01.dbf.

#3 - UNDO TABLESPACE

A undo tablespace is created to manage undo data if the Oracle database is being run in automatic undo management mode.

The syntax for the CREATE TABLESPACE statement 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 or BIGFILE is optional. It determines the size of the tablespace. If you omit this option, the Oracle database will use the default tablespace type.

• SMALLFILE is the most common tablespace size to create. It can contain 1,022 data or temp files (each file can be up to 4 million blocks in size).
• BIGFILE is a tablespace that contains only one data or temp file (this file can be up to 4 million blocks in size).

tablespace_name is the name of the tablespace to create.

Example - UNDO TABLESPACE

The following is a CREATE TABLESPACE statement 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 called tbs_undo_01 that is 5MB in size and has one data file called tbs_undo_01.f.