Oracle Tablespaces

Posted by Shaik Ahmed Ali on November 15, 2020 | No comments

This blog cover the creation and management of tablespaces and their associated datafiles.

You will learn how to create both locally managed and dictionary managed tablespaces.

·         Tablespaces are the logical unit of an oracle database, which stores all database data in it.

·         Each Oracle Tablespaces consist of files called the datafiles, which are physical structure that conform to the operating system in which Oracle is running.

·         Tablespace belongs to only 1 database with at least one datafile for storing the data.

·         Tablespace can have one or more datafiles but datafiles cannot have more than one tablespace.

·         Database can have more than one tablespace.

 

As per the above figure you can clearly understand that the tablespace can have more than one datafile

Types of Tablespaces  

There are three types of tablespaces in oracle they are:

1.Permanent

2. Undo

3.Temporary

1.Permanent tablespace:

A Tablespace which store the business data or you can say application data is the Permanent tablespace. Every user is assigned with the default permanent Tablespace.

2.Undo Tablespace:

These tablespace stores segment that can be retained if the transaction is rollback. These tablespace also provide the read consistency to the user for select statement for the table that have rows that are being modified. provide the ability   to rollback the transaction that fails to commit.

3. Temporary tablespace:

Temporary tablespace are used to store the temporary data, as would be created when the SQL statement do the sort operations. The data is store in this tablespace Is exist only for the duration of session or transaction.

Physical file of the temporary tablespace is referred to as a TEMP file instead of datafile.

Excluding these 3 tablespaces we have other tablespaces which are system and non-system tablespace.

 System Tablespace:

·         System tablespaces are those which stores the system data that include Tables, indexes, sequences, other objects that consist of data dictionary.

·         We should not allow the storage to user segment to SYSTEM tablespace.

Non-system tablespace:

·         Tablespace other than system tablespace are referred to as a non-system tablespace.

 

Syntax for creating the tablespace:

Create tablespace tablespacename datafile ‘location of datafile’ size 500M;

Autoextend tablespace:

·         you can set a tablespace to automatically extend itself by a specified amount when it reaches its size limit.

Note: Autoextend is the property of datafile not the tablespace, we refer to tablespace because datafile is associate with tablespace while creating tablespace we indicate whether these datafile is autoextend or not.

Tablespace space management:

Tablespace can either manage Locally managed or Dictionary Managed 

before going to read about tablespace management I want to give you brief explanation about the extent.

Extent: extent is logical unit of database storage space which have a number of contiguous data blocks

Locally managed: A tablespace which manages its own allocation of extent and maintain the track of free and used status of the block is knows as locally manages tablespace

Syntax:

Create tablespace userdata datafile ‘/u01/userdata.dbf’ size 500M extent management local uniform size 128K;

Dictionary managed: A tablespace which uses data dictionary to managed its extents is knows as dictionary tablespace. In earlier version we use dictionary tablespace as its effecting the performance to database because of recursive SQL oracle suggest to create local managed tablespace.

Syntax:

Create tablespace userdata datafile ‘/u01/userdata.dbf’ size 500M extent management Dictionary default storage (initial 1M next 1M pctincrease 0);