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.
Connect with me on: