• Tablespace is the primary logical
storage structure of the oracle database.
• A
tablespace is a
storage location where
the actual
data of database objects can be kept, e.g. Table , Index , views
etc.
• It consists of one
or more physical data-files.
• One oracle database may have
several tablespaces.
• Everytime we insert data into a table that
data is transferred to an underlying tablespace which will in turn write that data to the
underlying
data-files of that tablespace.
• More than one table can store their data
in the same tablespace.
• One table can exist
only in one tablespace at a time unless it is a partitioned
table.
• The size of a tablespace is the sum
total of the size of all the data-files under
that tablespace.
• A tablespace belongs to only
one database and has at least one data file.
• One
tablespace can have up to 1022 data-files,
for small file tablespace.
There are different types of tablespaces based on their purpose and type of data they store:
1. Permanent tablespace
- used to store data that can last beyond the duration of the session or transaction i.e. it stores data or schema objects that last as long as our database exists / permanent schema objects.
- Data here is stored in Datafiles
e.g. system tablespace , Users tablespace,SysAux tablespace
2. Temporary tablespace
- This stores temporary objects as its name indicates i.e. objects within this tablespace lasts for the duration of a session
- It is used for sort operations (order by , group by ) and hash joins in SQL
- Data here is stored in tempfiles
3. Undo tablespace
- undo tablespace is used for storing undo data
- It has two important functions :-
- For Read consistency for SELECT
- To rollback a transaction
A tablespace can also be classified as Big file tablespace and Small file tablespace based on the size of data they store.
Big file tablespaces are tablespaces with single datafile i.e. they contain a maximum of one datafile
- This single datafile is very large up to 128TB (depending on the block size of the database.
Small file tablespaces contain multiple datafiles up to 1022 files in one tablespace
Thanks for your visit!
