Home » RDBMS Server » Server Administration » Temporary tablespace space monitoring (, Linux)
Temporary tablespace space monitoring [message #607427] Thu, 06 February 2014 03:51 Go to next message
Messages: 104
Registered: September 2008
Senior Member

My question may seems 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%' 
 5  ;

------------------------------- ------------- ------------- --------------
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 ;

-------------------- -------------------------------- ---------- ---------- ---------- ----------
------------------------------------------------------------------------------------------------------------------------------------- -----------------
TEMP4 TEMP 40066.9844 40066.9844 0 100

TEMP TEMP 61440 61385 55 99.91

In the first query, I am using v$sort_segment view and in the second query, I am using v$temp_space_header. I understand the following from one of the posts of Michel.

This view displays aggregate information per file per LOCALLY MANAGED temporary tablespace regarding how much space is currently being used and how much is free as identified in the space header.

This view contains information about every sort segment in a given instance.

But my question is which view needs to be used for monitoring the space of the temporary tablespace usage. When I check from toad, I see the usage is nothing and it is reflecting the results of v$sort_segment. Do I need to consider for resizing the temp tablespaces ? Also, can you explain when we need to use v$sort_Segment and v$temp_space_header for finding the temporary tablespace usage.
Re: Temporary tablespace space monitoring [message #607428 is a reply to message #607427] Thu, 06 February 2014 04:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68598
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It depends on what you mean by usage.
By construction, used blocks in the temporary are never freed, they are reused when space is needed, this why you see free space=0 which does not mean tablespace is currently completely used just that all blacks have been used during this instance life.

It is like the recycle bin, the space seems to be used just because they are objects in the recycle bin but if space is needed then it is taken from the recycle bin.
In the same way, temporary blocks are not free but they are reusable.

Re: Temporary tablespace space monitoring [message #607434 is a reply to message #607428] Thu, 06 February 2014 08:58 Go to previous message
Messages: 104
Registered: September 2008
Senior Member

Thanks for your simple and clear explanation.
I feel that now I need to add a query to see the current usage for temp tablespaces also to have a clear information about size and space of temp tablespace.

Previous Topic: REASON="Job slave process was terminated"
Next Topic: Listener stops immediately after starting
Goto Forum:

Current Time: Thu Feb 29 10:23:58 CST 2024