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?
Thanks.