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 ;