Hello guys,
Hope you are doing well.
Today, we will talk about Oracle Tablespace and I will try to cover all related questions.
Although, as an Oracle developer we do not create a tablespace and add datafiles to it but we much have efficient knowledge about it. It is actually a part job of an Oracle DBA.

Let’s start with a definition of Tablespace and its importance in Oracle database.

What is a Tablespace?

A tablespace is a database storage unit that holds the datafiles and groups related logical structures together.
We can relate tablespace with a bag with multiple storage sections. Those storage sections are datafiles. Whenever we put something in our bag, we actually put into any of those storage sections. Though, while using the Oracle database does not allow us to choose any specific datafile.
So, we can say whenever we store some data into our database, Oracle database stores it into a datafile of the related tablespace.

You might be thinking, how does Oracle database determine which tablespace has to be used to store the data?
You will get an answer to this question very soon in the same blog.

Types of Tablespace

There are 3 types of Tablespace based on their use in Oracle database.

Permanent Tablespace

Permanent tablespaces are used to store user and application data. Oracle database uses permanent tablespaces to store permanent data such as system data. Each user is assigned a default permanent tablespace where it stores its data and fetches when required.
A permanent tablespace contains persistent schema objects and those objects in permanent tablespaces are stored in data files.

Oracle database (Oracle 12.2c) creates 3 permanent tablespaces by default when Oracle database is created.

1. SYSTEM
2. SYSAUX
3. USERS

Undo Tablespace

A database running in automatic undo management mode transparently creates and manages undo data into the undo tablespace. O5racle database uses undo data to roll back transactions to provide read consistency to help with database recovery and to enable features such as Oracle Flashback query. A database instance can have only one active undo tablespace.

Oracle database (Oracle 12.2c) creates one Undo tablespaces by default when Oracle database is created.

1. UNDOTBS1

Temporary Tablespace

Temporary tablespaces are used for storing temporary data, as would be created when SQL statements perform sort operations. An Oracle database gets a temporary tablespace when the database is created. You would create another temporary tablespace if you were creating a temporary group. Generally, we do not create an addition temporary tablespace. If we have an extremely large/big database, only in that case we might need an additional temporary tablespace.

A temporary tablespace contains schema objects only for the duration of a session. Objects in temporary tablespaces are stored in temp files. Once all the changes are committed by the user, Oracle database automatically cleans the data from the Temporary tablespace and sent the changes to a permanent tablespace.

Oracle database (Oracle 12.2c) creates one Undo tablespaces by default when Oracle database is created.

1. TEMP

How to create a Tablespace?

To create a tablespace, you must have CREATE TABLESPACE privilege or you must be connected to the database using SYSDBA (SYS user) privilege.
A tablespace must have at least one datafile all the time. It means while creating a tablespace we have to create a datafile with it.

Note:
If you are using Oracle 12.1c or 12.2c database, make sure database container is set to ‘ORCLPDB’ or any other PDB that you created when you installed the database.
You can use below queries to check current container and altering container.

 

--check the current container
SHOW CON_NAME;

--alter the container to ORCLPDB
ALTER SESSION SET container = ORCLPDB;

Creating a Permanent Tablespace

Using below query we will create a Tablespace named perm_tblspc with a datafile perm_df with size 50 MB.

 

CREATE TABLESPACE perm_tblspc
DATAFILE
'C:\APP\ORACLE\ORADATA\ORCL\perm_df.DBF'
SIZE 50M AUTOEXTEND ON;


Output

TABLESPACE PERM_TBLSPC created.

In the above code, I have set the initial size of datafile as 50MB and AUTOEXTEND ON. Once 50MB data has been loaded to the datafile Oracle database will extend its size automatically.

One more thing, I have mentioned above that I would be creating a datafile named perm_df but here I put it as ‘C:\APP\ORACLE\ORADATA\ORCL\perm_df.DBF’.
Actually, datafile name is still perm_df but as per Oracle guide lines we must provide a folder path where we want to put that datafile and datafile name must end with ‘.dbf’.

Once the tablespace is created in Oracle database, we assign QUOTA to oracle users. When user creates any object in the database, Oracle first checks the space and user QUOTA in the user’s default tablepace and if it finds no space in the default tablespace, it creates object in the another tablespace if available else slap an error.

We can assign QUOTA to users on multiple tablespaces.

I hope now you have got the answer.

Creating an UNDO Tablespace

We can create UNDO tablespace in the database with below given query. It is same as the query used to create permanent tablespace but with an additional keyword UNDO.

 

CREATE UNDO TABLESPACE undo_tblspc
DATAFILE
'C:\APP\ORACLE\ORADATA\ORCL\undo_df.DBF'
SIZE 50M AUTOEXTEND ON;


Output

TABLESPACE PERM_TBLSPC created.

Creating a Temporary Tablespace

While creating a Temporary Tablespace, there are two minor changes in the above query.
a). We will have to replace UNDO keyword with TEMPORARY.
b). As we are going to create a temporary tablespace, we will have to add a tempfile instead of datafile. So DATAFILE keyword will be replace by TEMPFILE keyword.

Let’s create a temporary tablespace.

 

CREATE TEMPORARY TABLESPACE temp_tblspc TEMPFILE
    'C:\APP\ORACLE\ORADATA\ORCL\temp_df.DBF' SIZE 50 M
        AUTOEXTEND ON;

Output

TABLESPACE PERM_TBLSPC created.

I hope you have learned the concept behind creating a tablespace.

You can check all tablespaces created in the database by querying below tables.

DBA_TABLESPACES
USER_TABLESPACES

How to alter a Tablespace

In this section, we will only learn how to change Tablespace status (offline/online).
It is pretty simple. You just need to use below query to change status of any tablespace.

 

ALTER TABLESPACE perm_tblspc OFFLINE;

--Output

TABLESPACE PERM_TBLSPC altered.

ALTER TABLESPACE undo_tblspc OFFLINE;

--Output

TABLESPACE PERM_TBLSPC altered.

We can change the status of Permanent Tablespace and UNDO tablespace with the same query.
In case of temporary tablespace, we cannot change its status to OFFLINE but we can change the status of its tempfile.
We will discuss this method in upcoming blog on DATAFILES.

How to DROP a Tablespace

To drop a tablespace we use DROP TABLESPACE statement.
There are no different keywords to drop temporary tablespace or undo tablespace.

 

-- DROP PERMANENT TABLESPACE
DROP TABLESPACE PERM_TBLSPC INCLUDING CONTENTS AND DATAFILES;

-- DROP UNDO TABLESPACE
DROP TABLESPACE UNDO_TBLSPC INCLUDING CONTENTS AND DATAFILES;

-- DROP TEMPORARY TABLESPACE
DROP TABLESPACE TEMP_TBLSPC INCLUDING CONTENTS AND DATAFILES;

INCLUDING CONTENTS keyword helps to remove all content of the tablespace such as Indexes.
DATAFILES keyword helps to drop all datafiles of the tablespace.

If you omit these keywords, the Oracle will drop only tablespace but its content and datafile will remain in the database but we/user can’t use them.
You can test this scenario by yourself.

Explaining tablespace is a very big topic itself so I have included very specific content to this blog to keep it easy to understand.

If you have any questions please comment below or write me an email.

If you like this blog, do share with your friends and colleagues on your social media.
For more updates join my facebook group and do like my facebook page.

Thank you,
Kapil Kumar

Sharing is caring!