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.
- A permanent tablespace are permanently stored in datafiles with persistent schema objects.
- 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.
- 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
|FORCE LOGGING
|DEFAULT {data_segment_compression} storage_clause
|[online|offline]
|[PERMANENT|TEMPORARY]
|extent_manager_clause
|segment_manager_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 logging 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 UNIFORM SIZE 128k;
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
CREATE TABLESPACE securespace DATAFILE ''C:\oracle\username\undo\secure01.dbf' SIZE 100M ENCRYPTION DEFAULT STORAGE(ENCRYPT);
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.
CREATE BIGFILE TABLESPACE bigtbs 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
logging
extent management local;