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 that Tablespaces 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

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.

Tablespaces

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.

tablespace in oracle by manish sharma

Types of Tablespaces

We can differentiate tablespaces 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

tablespace in oracle by manish sharma

 

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 tablespaces are the ones 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:

  1. Read consistency for SELECT statements that access tables which in turn consist of rows which are in the process of being modified.
  2. 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 of 1 datafile which means bigfile tablespaces are built on single data files which can be as big 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 much 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 a 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!