| temporary tablespace usage [message #320096] |
Wed, 14 May 2008 01:38  |
vijaychauhan
Messages: 106
Registered: December 2005
|
Senior Member |
|
|
Hi!
I wanted to find out the amount of space used in temporary tablespace so I, used the query
select tablespace_name, bytes_used/1024/1024, bytes_free/1024/1024 from v$temp_space_header;
it shows
temp 440 60
temp1_t 2048 0
them I tried the following query to check the user using the tablespace
select b.tablespace, (b.blocks*p.value)/1024/1024 , a.sid, a.username from sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter p
where p.name=b.session_addr;
it sdhows no rows
my question is
1) Why does it not show any user for the temporary tablespace?
2) Are the temporaray segments in use or are just they being not unallocated.
|
|
|
|
| Re: temporary tablespace usage [message #320322 is a reply to message #320096] |
Wed, 14 May 2008 12:47  |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
Once temp segments are allocated,they are retained by oracle.
So what you might see as used is actually free.
In your query you see,2048mb allocated.
To see if this allocated space is getting utilized,query
v$sort_usage view.
To get which user is using temp tablespace
SQL>select username,tablespace,extents from v$sort_usage;
|
|
|
|
|
|
|
|
|
|
| Re: temporary tablespace usage [message #320630 is a reply to message #320096] |
Thu, 15 May 2008 13:09 |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
This is the used space but is actually ready to be reused.
This is how oracle manages temp tablespaces.
They are never deallocated.
v$sort_usage,v$sort_segment gives the current usage of temp tablespace which is null in your case.
v$temp_space_header gives you the used space.This used space is actually the allocated space ready to be reused again.
So your temp space appears to be autoextended and seems to be full but actually it is not.Just like when we delete space is not deallocated but is available for future inserts.
You can run a query that does sorting and query the views to get the picture how oracle utilizes temp space.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
