Re: Calculation of remaining space in LMT's...

On Thu, 10 Aug 2006 05:22:01 -0700, Spendius wrote:

> There are unfortunately no such views as
> V$TEMP_SPACE_HEADER and
> V$TEMP_EXTENT_MAP (that underlyingly query X$KTFTHC
> and X$KTFTME structures) for regular tablespace datafiles...

If you are seeking to determine the amount of remaining free space in the temp tablespace(s), you may try with the following query:

select space,tot "Total",usd "Used",(1-(usd/tot))*100 "%Free"

        from (
           select
                tf.tablespace_name space,tf.total tot,nvl(tu.used,0) usd
           from (select tablespace_name,round(sum(bytes)/1048576) total
                 from dba_temp_files
                 group by tablespace_name) tf,
                 (select tablespace, 
                         round(sum(s.blocks*t.block_size)/1048576,2) used
                  from v$sort_usage s,dba_tablespaces t 
                  where s.tablespace=t.tablespace_name
                  group by tablespace) tu
           where tf.tablespace_name=tu.tablespace(+) )

Essentially, what this query does is the following: 1) It computes the total space in the temporary tablespace from

   dba_temp_files.
2) It computes used space in the temporary tablespaces from

   v$sort_usage
3) It joins the two subqueries and presents the information in almost

   useful format.

-- 
http://www.mgogala.com