Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle TEMP tablespace

Posted on 2014-09-03
4
Medium Priority
?
1,123 Views
Last Modified: 2014-09-08
Hi,
yesterday at 13:00 I have been getting the ORA-1652 error:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

Now I'd like to know what sessions have used the TEMP tablespace and how much space has been used by every session

What query can I use to get these informations?

My Oracle version is 10g

Thanks in advance!
0
Comment
Question by:ralph_rea
  • 2
4 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1200 total points
ID: 40300855
First view to look at is:
V$TEMPSEG_USAGE This view describes temporary segment usage.
http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2164.htm#REFRN30285

You can link it up to sessions and a few others if the session that allocated the space is still running.

There are a lot of scripts out there that have queries you can run to locate this information.

The thread below also mentions a couple of documents on Oracle Support that have information about this:
https://community.oracle.com/thread/2447146

Oracle Recommended Patches For Tablespace Monitoring using the Tablespace Space Used % metric [ID 849498.1]
 How Can Temporary Segment Usage Be Monitored Over Time? [ID 364417.1]
0
 

Author Comment

by:ralph_rea
ID: 40300865
the session that allocated the space is NOT still running and my oracle version is 10g (not 11g)
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40300889
>>my oracle version is 10g (not 11g)

I got that from the original question.  The links I posted was 10g's docs and the Support notes are not 11g specific.


Without the session active you might be able to use AWR or some of the historical views but I'm not that familiar with them.

I'll have to defer to other Experts that are more familiar with them.
0
 
LVL 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 800 total points
ID: 40309319
some versions of oracle db have a bug when autoextend of temp datafiles are set and max is reached.
then you can get this error too.
the temp doesn't have to be full to get this error.

set the autoextend property off for all the tempfiles to resolve the bug.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question