Solved

Oracle TEMP tablespace

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

867 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now