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 Tablespace in Oracle database. 
Oracle database stores schema objects such as tables, indexes, Views etc. logically into the tablespace and physically in datafiles associated with the 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 of 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 the proper functioning of your database system. I suggest you 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 the logical entity in our 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. We 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 in Oracle Database

We can differentiate tablespaces on the basis of two factors:

  1. Type of Data
  2. Size of Data

Type of data consists of 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 In Oracle Database

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 In Oracle Database

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 In Oracle Database

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 In Oracle Database

The new concept started from Oracle 10g. Big file tablespace is best suited for storing large amounts of data. Big file tablespace can have a 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 In Oracle Database

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 do 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!

If you still have any doubts then connect with me on my Facebook & Twitter.

Thanks & Good Speed