Create Tablespaces in Oracle

A tablespace is a storage location where the actual data underlying database objects can be kept, it is a logical storage unit within an Oracle database and not visible in the file system of the machine on which the database resides.

We can simply Use the CREATE TABLESPACE statement to create a tablespace, which is an allocation of space in the database that can contain schema objects.

  1. A permanent tablespace are permanently stored in datafiles with persistent schema objects.
  2. An undo tablespace is a type of permanent tablespace used by Oracle Database to manage undo data if you are running your database in automatic undo management mode. Oracle strongly recommends that you use automatic undo management mode rather than using rollback segments for undo.
  3. A temporary tablespace are temporary stored in tempfiles which is visible only for the duration of a session.

The definition of creating a Tablespace

CREATE [UNDO]  TABLESPACE tablespace_name

[DATAFILE datefile_spec1 [,datefile_spec2] ......

[ { MININUM EXTENT integer [k|m]

| BLOCKSIZE integer [k]

|logging clause


|DEFAULT  {data_segment_compression} storage_clause





Example of creating Permanent tablespace
The datafile specifics the date file name to be newly created and used to store object.

create tablespace tablespace_01
  datafile 'C:\oracle\username\oradata\ag92g.dbf'
  size 1024M
  autoextend on
  next 32m maxsize 2048m
  extent management local;
create tablespace data datafile 'C:\oracle\username\oradata\data.dbf'
size               1024M

Example of creating Temporary tablespace

From above definition we know Objects in temporary tablespaces are stored in tempfiles. it is not datafiles.

create temporary tablespace temp_tablespace
  tempfile 'C:\oracle\username\tempdata\temp_tablespace.dbf'
  size 32m
  autoextend on
  next 32m maxsize 2048m
  extent management local;

Example of creating Undo tablespace
It is simple, Just it is same as above.

create undo tablespace ts_undo
  datafile  'C:\oracle\username\undo\ts_undo.dbf'
  size 100M;

Example of creating Encrypted Tablespaces

DATAFILE ''C:\oracle\username\undo\secure01.dbf' SIZE 100M

Example of Creating a Bigfile Tablespace
A bigfile tablespace is a tablespace with a single, but very large (up to 4G blocks) datafile. Traditional smallfile tablespaces, in contrast, can contain multiple datafiles, but the files cannot be as large. If your oracle db is designed with larger data number or a lot of index to be created, you can consider creating as a bigfile tablespaces.

DATAFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G

You can specify SIZE in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T).

Example of Creating single Tablespace with multiple data files

One tablespace does not restrict to refer one data file.  More than one datafile can be created with a single create tablespace command as well:

create tablespace ts_01
  datafile 'C:\oracle\username\undo\ts_01.dbf' size 4M autoextend off,
           'C:\oracle\username\undo\ts_02.dbf' size 4M autoextend off,
           'C:\oracle\username\undo\ts_03.dbf' size 4M autoextend off
  extent management local;

Оцените статью
Добавить комментарий

Your email address will not be published. Required fields are marked *