What is tablespace management in Oracle?

In this article, we are going to discuss how to manage space in tablespace (Managed space in Tablespace). Oracle has two types of tablespaces Locally Managed Tablespace and Dictionary Managed tablespace.

Locally Managed Tablespace

The Locally Managed Tablespace is the default type of tablespace and locally managed tablespace extents are managed within the tablespace via bitmap. Data storage hierarchy in locally tablespace is:

Datafile – segment – Extent – Block, locally tablespace records extents allocation in the tablespace header it’s called a locally managed tablespace.

How to create Locally Managed Tablespace

To create a locally managed tablespace use below.

create tablespace tb_name
datafile ‘/u01/app/oracle/oradata/db11g/tb_name01.dbf’
size 10m
auto extend on
extent management local; -- (This line optional, default tablespace type is locally managed )

Dictionary Managed Tablespace

The Dictionary Managed Tablespace is supported backward compatibility, in dictionary managed tablespace extents are managed by the data dictionary, Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary, or in the tablespace itself. Tablespaces that record extent allocation in the dictionary, are called dictionary-managed tablespaces.

How to create Dictionary Managed Tablespace

To create dictionary managed tablespace use below.

create tablespace tb_name
datafile ‘/u01/app/oracle/oradata/db11g/tb_name01.dbf’
size 10m
extent management dictionary;

Note: – You can change Dictionary Managed tablespace into locally Managed tablespace but you don’t change locally managed tablespace into Dictionary tablespace.

How to change Dictionary Managed tablespace into Locally Managed Tablespace and vice versa?

Convert Dictionary to locally

exec dbms_space_admin.tablespace_migrate_to_local (‘TB_NAME’);

Convert Locally to Dictionary

exec dbms_space_admin.tablespace_migrate_from_local (‘TB_NAME’);

I hope you understand about Managed space in Tablespace. If yes please write your review in the comment section.

How to install oracle 11g on Linux

Connect with me on:

Instagram: https://www.instagram.com/digitalshripal
Linkedin: https://www.linkedin.com/in/shripal-singh
Twitter: https://twitter.com/ocptechnology
Facebook: https://www.facebook.com/ocptechnology
YouTube:https://www.youtube.com/ocptechnology