Forum Stats

  • 3,873,347 Users
  • 2,266,538 Discussions
  • 7,911,514 Comments

Discussions

Reorganize storage structure

ProDBA
ProDBA Member Posts: 120 Blue Ribbon

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.

Tagged:

Best Answer

Answers