query to find the temp usage
oracle 10.2.0.4 on win2008
I got two queries from google to find the free space in temp tablespace. but both are showing different results.
Please let me know which one is showing the correct space usage in temp tablespace.
query 1 :
------------------------
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
query 2 :
----------------------
select tablespace_name,sum(bytes_used/1024/1024),sum(bytes_free/1024/1024) from v$temp_space_header group by tablespace_name;
Edited by: %bala% on Apr 30, 2013 8:31 PM