topic How to find the free space in a tablespace in Operating System - HP-UX

How to find the free space in a tablespace

Vinu Neelakandhan — Wed, 29 Aug 2001 10:16:32 GMT

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

Re: How to find the free space in a tablespace

Thierry Poels_1 — Wed, 29 Aug 2001 10:41:49 GMT

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.

Re: How to find the free space in a tablespace

Kaido — Wed, 29 Aug 2001 14:22:51 GMT

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

Re: How to find the free space in a tablespace

Brian Crabtree — Wed, 29 Aug 2001 18:48:41 GMT

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;

Re: How to find the free space in a tablespace

Simeon Fox — Thu, 30 Aug 2001 08:56:08 GMT

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

Re: How to find the free space in a tablespace

Kaido — Fri, 31 Aug 2001 07:16:39 GMT

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.