Database Administration (MOSC)

MOSC Banner

Temporary tablespace usage monitoring

edited February 2014 in Database Administration (MOSC) 3 commentsAnswered

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  ;


Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center