Neat. Nice Job.

-----Original Message-----
From: tboss
[mailto:oracledba-ezmlmshield-x91702256.[Email address protected]
Sent: Wednesday, June 30, 2004 11:33 AM
To: LazyDBA Discussion
Subject: Re: What query to use to check available space in datafiles and
t hen


Here's two queries I like:

SELECT Total.name "Tablespace Name",
Free_space, (total_space-Free_space) Used_space, total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name = Total.name

and

select a.tablespace_name,megs_allocated,megs_used,
round(megs_used/b.megs_allocated, 2)*100 pct_used
from
(select tablespace_name,sum(bytes)/(1024*1024) megs_used
from dba_extents group by tablespace_name) a,
(select tablespace_name,sum(bytes)/(1024*1024) megs_allocated
from dba_data_files group by tablespace_name) b
where a.tablespace_name=b.tablespace_name

Note: neither of these get you temp tablespace.

Todd


Oracle LazyDBA home page