Oracle stores data logically in tablespaces and physically in datafiles associated with the corresponding 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.

By using tablespaces, you can perform the following operations:

• Control the storage size allocated for the database data.
• Grant specific space quotas to the database users.
• Control the availability of data by taking tablespaces online or offline (more on this later).
• Improve the performance of the database by allocating data storage across devices.
• Perform partial database backup or recovery.

Oracle comes with the following default tablespaces: SYSTEM, SYSAUX, USERS, UNDOTBS1, and TEMP.

• The SYSTEM and SYSAUX tablespaces store system-generated objects such as data dictionary tables. And you should not store any object in these tablespaces.
• The USERS tablespace is helpful for ad-hoc users.
• The UNDOTBS1 holds the undo data.
• The TEMP is the temporary tablespace which is used for storing intermediate results of sorting, hashing, and large object processing operations.

We can differentiate tablespaces on the basis of two factors, Type of Data and Size of Data

Type of data consists of 3 kinds of tablespace including:

• Permanent Tablespace: Contains persistent schema object which means the data stored in the permanent tablespace.
• Temporary Tablespace: ones which contain schema objects only for the duration of a session or a transaction. (TEMP Tablespace)
• Undo Tablespace: It is used to manage undo data. Read consistency for SELECT statements and rollback a transaction.

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

• Big file tablespace: Introduced in 10g, have a maximum of 1 datafile which can grow upto 32TB in size.
• Small file tablespace: It is normal type of tablespace which can have many datafiles of size upto 32GB.