Home » RDBMS Server » Server Administration » temporary tablespace usage
temporary tablespace usage [message #320096] Wed, 14 May 2008 01:38 Go to next message
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 Go to previous messageGo to next message
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 #320487 is a reply to message #320322] Thu, 15 May 2008 04:41 Go to previous messageGo to next message
vijaychauhan
Messages: 106
Registered: December 2005
Senior Member
hi!
If I, use v$sort_usage, it will not show any rows, it says no rows selected.
Also I restarted database, but it does show any change in v$temp_space_header view.
Re: temporary tablespace usage [message #320529 is a reply to message #320096] Thu, 15 May 2008 06:46 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
SO no user is using temp tablespace.

Re: temporary tablespace usage [message #320591 is a reply to message #320529] Thu, 15 May 2008 10:31 Go to previous messageGo to next message
vijaychauhan
Messages: 106
Registered: December 2005
Senior Member
Then why is it showing the same number of bytes_used even after startup?
Re: temporary tablespace usage [message #320630 is a reply to message #320096] Thu, 15 May 2008 13:09 Go to previous messageGo to next message
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.
Re: temporary tablespace usage [message #320713 is a reply to message #320096] Fri, 16 May 2008 01:19 Go to previous messageGo to next message
myana.srinivas1
Messages: 9
Registered: April 2008
Location: India
Junior Member
I think we can also use the following statement to know which user is being allocated to which tablespace ...


select username,temporary_tablespace,default_tablespace from dba_users;
Re: temporary tablespace usage [message #320884 is a reply to message #320096] Fri, 16 May 2008 10:30 Go to previous messageGo to next message
mnsinger
Messages: 1
Registered: May 2008
Junior Member
Hey guys ... I found a great link that explains and gives helpful queries about temp space stuff.

http://www.dbspecialists.com/presentations/temp_space.html
Re: temporary tablespace usage [message #320888 is a reply to message #320884] Fri, 16 May 2008 10:38 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Previous Topic: Sybase to oracle migration
Next Topic: after run CREATE DATABASE statement, where can I find the log file?
Goto Forum:
  


Current Time: Sat Dec 10 12:52:18 CST 2016

Total time taken to generate the page: 0.11597 seconds