PLSQL: CREATE TABLESPACE statement (2024)

This Oracle tutorial explains how to use the Oracle CREATE TABLESPACE statement with syntax and examples.

Description

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.

Syntax

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

A tablespace that contains 1,022 data or temp files (each file can be up to 4 million blocks in size). This is the most common tablespace size to create.

BIGFILE

A tablespace that contains only one data or temp file (this file can be up to 4 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 name 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 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.

Syntax

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

A tablespace that contains 1,022 data or temp files (each file can be up to 4 million blocks in size). This is the most common tablespace size to create.

BIGFILE

A tablespace that contains only one data or temp file (this file can be up to 4 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 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

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

Syntax

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

A tablespace that contains 1,022 data or temp files (each file can be up to 4 million blocks in size). This is the most common tablespace size to create.

BIGFILE

A tablespace that contains only one data or temp file (this file can be up to 4 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 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.