Skip to Main Content

Reorganize storage structure

ProDBAJul 24 2022

Due to a project requirement, we need to analyse the internal storage structure of database. Initially we need to see how many tables a schama has, how many bytes the table have and also the free space in the blocks of the tables.
From the following query, we can get the name of the tables and their respective size within a schema:

select   table_name, bytes, round(bytes/1047586, 2) as MB
from   dba_tables t
inner join dba_segments s
on table_name =segment_name and t.owner =s.owner
where segment_type='TABLE'
and s.owner='SCHEMA_NAME';

The question is, how we can get the total and free bytes for each table? and how can we free/reclaim space afterwards?


This post has been answered by JohnWatson2 on Jul 25 2022
Jump to Answer
Post Details
Added on Jul 24 2022