Temporary tablespace usage monitoring
Hi,
My question may seem to be silly but I am confused. I have a query with regards to temporary tablespace usage views.
I am using below queries and getting different results.
select tablespace_name , (free_blocks*8)/1024/1024 FreeSpaceInGB, (used_blocks*8)/1024/1024 UsedSpaceInGB, (total_blocks*8)/1024/1024 TotalSpaceInGB from v$sort_segment where tablespace_name like '%TEMP%' 2 3 4 5 ;
TABLESPACE_NAME FREESPACEINGB USEDSPACEINGB TOTALSPACEINGB
------------------------------- ------------- ------------- --------------
TEMP 59.9208984 .0234375 59.9443359
TEMP4 39.125 0 39.125
SQL> select sh.tablespace_name, 2 'TEMP', 3 SUM(sh.bytes_used+sh.bytes_free)/1024/1024 total_mb, 4 SUM(sh.bytes_used)/1024/1024 used_mb, 5 SUM(sh.bytes_free)/1024/1024 free_mb, 6 ROUND(SUM(sh.bytes_used)/SUM(sh.bytes_used+sh.bytes_free)*100,2) pct_used, 7 '['||DECODE(SUM(sh.bytes_free),0,'XXXXXXXXXXXXXXXXXXXX', 8 NVL(RPAD(LPAD('X',(TRUNC(ROUND((SUM(sh.bytes_used)/SUM(sh.bytes_used+sh.bytes_free))*100,2)/5)),'X'),20,'-'), 9 '--------------------'))||']' 10 FROM v$temp_space_header sh 11 GROUP BY tablespace_name 12 --order by TOTAL_mb desc 13 order by pct_used desc 14 ;