query to check tablespace size and freespace

547581

hi experts
i am using oracle 9i .
want ot check freespace and size of tablespace could u plz tell me the query

thanks
varun

ViragSharma

col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;

Nicolas Gasparotto

Virag,

What about a free tablespace (no segment) ? You may want to add an external join.

What we can do :

select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb

from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space

       from dba_free_space

       group by tablespace_name) a,

      (select tablespace_name, sum(bytes)/1024/1024 as tbs_size

       from dba_data_files

       group by tablespace_name) b

where a.tablespace_name(+)=b.tablespace_name;

Nicolas.

44863

add following for temp files -

select sum(bytes)/1024 kbytes_alloc, tablespace_name
from sys.dba_temp_files
group by tablespace_name

the new query will be

select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from
(select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name
UNION
select tablespace_name, sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b
where a.tablespace_name(+)=b.tablespace_name;


cheers

Nicolas Gasparotto

Good point, but who does matter the free space of temp tbs ?

Nicolas.

44863

It is about the size of DB.

ViragSharma

Thanks Nicolas,

I use this script for monitoring purpose ( tablespace usage should not cross 90%)
I never thought of this possibility/case, that tablespace can exists without any segments.

Thanks once again

Cheer,
Virag