0

I would like to understand in what sort of situations or what issues could cause TEMP tablespace to run out of space.

  • Is it too much sorting occur on TEMP?
  • How do I identify such issue from AWR?
  • And how usually it can be resolved?

My Oracle Database instance is running on version 18c.

I am a DBA who mainly manages the operational side of the DB. I don't do much development / code writing. I would like to understand more, so I could provide some advice to the developers.

1 Answer 1

0

As you noted, TEMP space is used when sorting datasets too large for memory. It is also used to materialize intermediate data sets (as from subqueries or CTE) whether sorted or not, for index creation, or for temporary LOB storage.

There isn't a lot you can do to avoid using it: it's automated by Oracle. Most often, your options are "process less data at once" or "make TEMP bigger". You can either manually enlarge one or more datafiles in your TEMP tablespace, set the datafiles to autoextend up to a limit, or add more datafiles.

You can monitor usage by session with this:

SELECT    S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
          P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
          COUNT(*) statements
 FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
 WHERE    T.session_addr = S.saddr
 AND      S.paddr = P.addr
 AND      T.tablespace = TBS.tablespace_name
 GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
          P.program, TBS.block_size, T.tablespace
 ORDER BY sid_serial;

And then check v$session or use a tool like SQL Developer's Instance Viewer for information about current SQL to see which SQL command is causing the issue. Note that it may not be just one operation causing the issue: it could also be the cumulative effect of many operations running simultaneously.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.