Tablespace In Oracle Database

By Manish Sharma

Tablespace- The Introduction

I promised this tutorial a while ago but never had a chance to do it since then. However here I am today, finally starting a new series on Tablespaces in Oracle database.

Oracle database stores schema objects such as tables, indexes, Views etc. logically into the tablespace and physically in datafiles associated with corresponding tablespace. So we can say Tablespace are logical storage units made up of one or more datafiles.

I know this whole deal with datafiles and tablespaces is slightly confusing but don’t worry. Let me try to explain it to you in detail.


Datafiles are physical files stored on your disk created by oracle database and has .dbf extension. These files are not only capable for storing tables but also indexes, synonyms, views and other schema objects created by you. These are physical files because they have existence on your OS’s file system and you can see them. These files are written by database writer (DBWR) processes and used by oracle database for proper functioning of your database system. Do not try to modify these files manually. A datafile cannot be shared between multiple tablespaces which means that every datafile belongs to a specific tablespace.


Then comes the Tablespace. Tablespaces are logical entity in your database and logically organized data which is physically stored in datafiles. They are called logical storage units because they are not visible in the OS’s file system. A tablespace belongs to only one database, and has at least one datafile that is used to store data for the associated tablespace. You can also define tablespaces as logical storage units made up of one or more datafiles. One tablespace can have up to 1022 datafiles this number also depends upon your OS.


Types of Tablespaces

We can differentiate tablespace on the basis of two factors

  1. Type of Data
  2. Size of Data

Type of data consists 3 kinds of tablespace including

  1. Permanent Tablespace
  2. Temporary Tablespace
  3. Undo Tablespace

And on the basis of Size of Data we have 2 kinds of tablespace

  1. Big file tablespace
  2. Small file tablespace


Permanent Tablespace

It is the tablespace which contains persistent schema object which means the data stored in the permanent tablespace persists beyond the duration of a session or transaction. Objects in permanent tablespaces are stored in datafiles.

Temporary tablespace

On The contrary temporary tablespace are the tablespaces which contain schema objects only for the duration of a session which means that data stored in the temporary tablespace exists only for the duration of a session or a transaction. Objects in temporary tablespaces are stored in tempfiles.

Undo Tablespace

Then there comes Undo tablespace. Undo tablespace is a special type of tablespace used by oracle database to manage undo data if you are running your database in automatic undo management mode. Undo tablespace stores data permanently which means that undo tablespace are permanent in nature. Undo tablespace play a vital role in providing

  • Read consistency for SELECT statements that access tables which in turn consist of rows which are in the process of being modified.

  • The ability to rollback a transaction that has failed to commit.
Next we have Big-file tablespace and small file tablespace.

Big-File tablespace

The new concept started from Oracle 10g. Big file tablespace is best suited for storing large amounts of data. Big file tablespace can have maximum 1 datafile which means bigfile tablespaces are built on single data files which can be as many as 232 data blocks in size. So, a bigfile tablespace that uses 8KB data blocks can be as much as 32TB in size.

Small-File tablespace

This is the default type of tablespace in oracle database. Small file tablespace can have multiple datafiles and each datafile can be as many as 222 data blocks in size. A small file tablespace can have maximum up to 1022 data files but this number depends on your Operating system also.

Info Byte: You can create Permanent, temporary or undo tablespace either as big-file tablespace or small-file tablespace but by default they are always small-file tablespace.

Interview Questions

Q: What are the default tablespaces in Oracle Database?
A: The SYSTEM and SYSAUX tablespaces are always created when the database is created. One or more temporary tablespaces are usually created in a database along with an undo tablespace and several application tablespaces. Because SYSTEM and SYSAUX are the only tablespaces always created with the database, they are the default tablespaces.

Q: What type of tablespace does SYSTEM and SYSAUX belong to?
A: SYSTEM and SYSAUX are always created as SMALLFILE tablespace.

That’s all about the introduction of Tablespaces. I hope you found this article on Tablespaces in Oracle Database helpful. Kindly please share it on your social network and help me reach out to more people. Thanks & have a great day!

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

SQL Script used in Video and in this article

I have used copy cloud to share these resourses. You can also join copy cloud and get 15GB free cloud storage for lifetime. If you will use this referal link You will get 5GB extra free cloud storage means total 20GB free cloud storage.

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn
  •          View Manish Sharma's profile on LinkedIn