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

>
> I am sure you meant
>
> select file_id, sum(bytes) from
> dba_free_space
> group by file_id;
>
>
> -----Original Message-----
> From: MOLO AWONO Joseph Bertrand
> [mailto:oracledba-ezmlmshield-x42415208.[Email address protected]
> Sent: Wednesday, June 30, 2004 6:55 AM
> To: LazyDBA Discussion
> Subject: RE: What query to use to check available space in datafiles and
> t hen tablespaces
>
>
> High,
> In addition to what have been said, here is the sql scripts:
>
> select file_name, sum(bytes) from
> dba_free_space
> group by file_name;
>
> select tablespace_name, sum(bytes) from
> dba_free_space
> group by tablespace_name;
>
>
> -----Message d'origine-----
> De : Ganesh
> [mailto:oracledba-ezmlmshield-x94187832.[Email address protected]
> Envoyé : mercredi 30 juin 2004 11:41
> À : LazyDBA Discussion
> Objet : RE: What query to use to check available space in datafiles and
> t hen tablespaces
>
>
> DBA_Free_Spaces will have File Wise Free Space Available.. If you Crunch it
> to the Tablespace Level u have got what you want ..
>
> HTH
>
> Cheers,
> Ganesh R
>
>
> -----Original Message-----
> From: Aldo Judeel
> [mailto:oracledba-ezmlmshield-x62854514.[Email address protected]
> Sent: 30 June 2004 11:27
> To: LazyDBA Discussion
> Subject: What query to use to check available space in datafiles and then
> tablespaces
>
>
> Could someone help me with a query that will enable me
>
> To check the free/available space in the datafiles and then
>
> Tablespaces again.
>
>
>
> I have tried dba_free_space but I cant seem to find what I'm looking for.
>
>
>
> The other way could be to use dba_segments and minus it from dba_tablespaces
>
> But I'm sure there must be a quicker way
>
>
>
> Thanks
>
> Aldo Judeel
>
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: send a blank email to oracledba-[Email address protected]
> By using this list you agree to these
> terms:http://www.lazydba.com/legal.html
>
>
>
> ------------------------------------------------------------------------
> For more information about Barclays Capital, please
> visit our web site at http://www.barcap.com.
>
>
> Internet communications are not secure and therefore the Barclays
> Group does not accept legal responsibility for the contents of this
> message. Although the Barclays Group operates anti-virus programmes,
> it does not accept responsibility for any damage whatsoever that is
> caused by viruses being passed. Any views or opinions presented are
> solely those of the author and do not necessarily represent those of the
> Barclays Group. Replies to this email may be monitored by the Barclays
> Group for operational or business reasons.
>
> ------------------------------------------------------------------------
>
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: send a blank email to oracledba-[Email address protected]
> By using this list you agree to these terms:http://www.lazydba.com/legal.html
>
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: send a blank email to oracledba-[Email address protected]
> By using this list you agree to these terms:http://www.lazydba.com/legal.html
>
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: send a blank email to oracledba-[Email address protected]
> By using this list you agree to these terms:http://www.lazydba.com/legal.html
>


Oracle LazyDBA home page