[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1130
  • Last Modified:

Oracle TEMP tablespace

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
ralph_rea
Asked:
ralph_rea
  • 2
2 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
ralph_reaAuthor Commented:
the session that allocated the space is NOT still running and my oracle version is 10g (not 11g)
0
 
slightwv (䄆 Netminder) Commented:
>>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
 
Geert GruwezOracle dbaCommented:
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now