Solved

Oracle TEMP tablespace

Posted on 2014-09-03
4
1,038 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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 300 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 76

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 37

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 200 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

770 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