troubleshooting Question

To catch who taking a big temp space

Avatar of luyan
luyan asked on
Oracle Database
6 Comments3 Solutions877 ViewsLast Modified:
When Temp space is allocated, system will retain it.  So once allocated, the TEMP tablespace will always appear being "used" and not released. So it is normal to see temp appears "full".  We can get to know what is really used and what is not through checking v$sort_usage.

Question 1: I used the following query to catch who taking a big temp space.
SELECT s.username,u.SESSION_NUM, u.tablespace, u.contents, u.extents, u.blocks FROM v$session s,
v$sort_usage u WHERE s.saddr=u.session_addr;
Do you have any other good idea to catch the information?

Question 2: Do you have a long query with many join takes a big space of TEMP tablespace? I want to test catching the information who taking a big temp space.


Join our community to see this answer!
Unlock 3 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros