How to find the free space in a tablespace

[Vinu Neelakandhan]

Hello :

Using sql scripts, how can I find the free space and occupied space in bytes (or MB) in a tablespace (NOT the extents free and used)? I would appreciate any pointers to this.

Thanks,
Vinu

[Thierry Poels_1]

Hi,
on one side you have unallocated extents in your tablespace which can be used for anything you want (ref. dba_free_space).

On the other side you have tables with extents allocated which are filled up or still free. Compute statistics for the table and check empty_blocks in user_tables (or dba_tables).

good luck,
Thierry.

[Kaido]

Hi,

Try this

SELECT f.tablespace_name, SUM(f.bytes)/(1024*1024) "Free (MB)",
Total_MB
FROM sys.dba_free_space f,
(select TABLESPACE_NAME, sum(BYTES)/(1024*1024) Total_MB
from dba_data_files
group by TABLESPACE_NAME
) d
where f.tablespace_name=d.tablespace_name
GROUP BY f.tablespace_name, d.Total_MB

[Brian Crabtree]

You will also want to watch out for fragmentation on the tablespace. This can lead to a high free space value, but also a high number of extents. You might want to consider running something like this to see if there is a high degree of fragmentation:

select tablespace_name,count(bytes),sum(bytes) from dba_free_space group by tablespace_name;

[Simeon Fox]

Regarding the fragmentation issue, it is useful also to display the largest available chunk of freespace in each tablespace :

select tablespace_name,
round(sum(bytes)/(1024*1024),1) free,
max(round(bytes/(1024*1024),1)) largest
from dba_free_space
group by tablespace_name

[Kaido]

Hi,

My first select was more or less like statistical select,
but if we talking about fragmentation then you
must check your db extents also:
select owner,segment_name, next_extent, s.tablespace_name,max_free_bytes
from sys.dba_segments s,
(select tablespace_name,max(bytes) max_free_bytes
from sys.dba_free_space
group by tablespace_name) f
where s.next_extent > f.max_free_bytes
and s.tablespace_name=f.tablespace_name

If you receive any rows then trouble is waiting for you.
Could be next help you:

alter tablespace tablespace_name coalesce

tablespace_name is the same what you receive before.