Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

query to find the temp usage

cherry_tApr 30 2013 — edited May 1 2013
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 29 2013
Added on Apr 30 2013
7 comments
716 views