Oracle CREATE TABLESPACE operator

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

The CREATE TABLESPACE operator can be used to create three types of tabular spaces

• Permanent Tablespace
• Temporary Tablespace
• Undo Tablespace

We will consider all 3 types of tabular spaces.

1. Permanent Tablespace

A persistent table space contains permanent schema objects, which are stored in data files.

The CREATE TABLESPACE instruction syntax for creating a permanent table space:

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 | } [ 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 [ {COMP | NOCOMPRESS } ]
storage_clause
| { ONLINE | OFFLINE }
|
{LOCAL
[ AUTOALLOCATE
| UNIFORM
[ SIZE integer [ K | M | G | T | P | E ] ]
]
| DICTIONARY
}
| SPACE MANAGEMENT { AUTO | MANUAL }
| FLASHBACK { ON | OFF }
[ MINIMUM EXTENT integer [ K | M | G | T | P | E ]
| BLOCKSIZE integer [ K ]
| { LOGGING | NOLOGGING }
| FORCE LOGGING
| DEFAULT [ {COMP | NOCOMPRESS } ]
storage_clause
| { ONLINE | OFFLINE }
|
{LOCAL
[ AUTOALLOCATE | UNIFORM [ SIZE integer [ K | M | G | T | P | E ] ] ]
| DICTIONARY
}
| SPACE MANAGEMENT { AUTO | MANUAL }
| FLASHBACK { ON | OFF }
]
}

• SMALLFILE is a table space containing 1022 data files or temporary files (each file can contain up to 4 million blocks). This is the most common size for creating a tabular space.
• BIGFILE is a table space containing only one data file or temporary file (each file can contain up to 4 million blocks).

TIP. If you omit SMALLFILE or BIGFILE, the Oracle database will use the default table space type.

• tablespace_name is the name of the table space you are creating.
• storage_clause – the syntax for storage_clause:

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 [ 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 [ K | M | G | T | P | E ] | NULL ]
| BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
]
)

Example PERMANENT TABLESPACE

Below is the CREATE TABLESPACE operator, which creates a simple permanent table space:

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

This CREATE TABLESPACE operator creates a permanent table space tbs_perm_01 that has one data file tbs_perm_01.dat.

Below is a CREATE TABLESPACE statement that creates a persistent table space that will expand when more space is needed:

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

This CREATE TABLESPACE operator creates a permanent table space tbs_perm_02 with one data file tbs_perm_02.dat. When more space is needed, 10M extents will be automatically added until 200MB is reached.

Below is the CREATE TABLESPACE operator, which creates a permanent BIGFILE table space that will expand when more space is needed:

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

This CREATE TABLESPACE statement creates a permanent BIGFILE table space named tbs_perm_03 that has one data file named tbs_perm_03.dat.

2. Temporary Tablespace

The temporary tabular space contains schema objects that are stored in temporary files that exist during the session.

Syntax of the CREATE TABLESPACE operator when creating temporary tabular space:

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 | ] ] [ 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 is a table space containing 1022 data files or temporary files (each file can contain up to 4 million blocks). This is the most common size for creating a tabular space.
• BIGFILE is a table space containing only one data file or temporary file (each file can contain up to 4 million blocks).

TIP. If you omit SMALLFILE or BIGFILE, the Oracle database will use the default table space type.

• tablespace_name is the name of the table space you are creating.

Example TEMPORARY TABLESPACE

Below is the CREATE TABLESPACE operator that creates a temporary tabular space:

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

This CREATE TABLESPACE operator creates a temporary table space tbs_temp_01 with one temporary file tbs_temp_01.dbf.

3. UNDO TABLESPACE

For undo tabular undo space, if the Oracle database runs in automatic undo mode.
The undo table space is created to manage undo data if the Oracle database runs in automatic undo management mode.

Syntax of CREATE TABLESPACE operator when creating Undo table space:

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 | ] ] [ 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 is a table space containing 1022 data files or temporary files (each file can contain up to 4 million blocks). This is the most common size for creating a table space.
• BIGFILE is a table space containing only one data file or temporary file (each file can contain up to 4 million blocks).

TIP. If you omit SMALLFILE or BIGFILE, the Oracle database will use the default table space type.

• tablespace_name is the name of the table space you are creating.

Example – UNDO TABLESPACE

Below is the CREATE TABLESPACE operator, which creates Undo table space:

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

This CREATE TABLESPACE operator creates a 5MB tbs_undo_01 table space and has one tbs_undo_01.f data file.

Oracle Database tutorial: How to create Tablespace using SQL