What is a tablespace in oracle?

      

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