Discover what process/query is using oracle temp tablespace.

Question

Oracle FAQ defines temp table space as follows:

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory, space will be allocated in a temporary tablespace for doing the sort operation.

That's great, but I need more detail about what exactly is using the space. Due to quirks of the application design most queries do some kind of sorting, so I need to narrow it down to client executable, target table, or SQL statement.

Essentially, I'm looking for clues to tell me more precisely what might be wrong with this (rather large application). Any sort of clue might be useful, so long as it is more precise than "sorting".

Answer 1

I'm not sure exactly what information you have to hand already, but using the following query will point out which program/user/sessions etc are currently using your temp space.

SELECT   b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
       , a.SID
       , a.serial#
       , a.username
       , a.osuser
       , a.program
       , a.status
    FROM v$session a
       , v$sort_usage b
       , v$process c
       , v$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
ORDER BY b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;

Once you find out which session is doing the damage, then have a look at the SQL being executed, and you should be on the right path.

Answer 2

One rule of thumb is that almost any query that takes more than a second probably uses some TEMP space, and these are not the just ones involving ORDER BYs but also:

1. GROUP BYs (SORT GROUPBY before 10.2 and HASH GROUPBY from 10.2 onwards)
2. HASH JOINs or MERGE JOINs
3. Global Temp Tables (obviously)
4. Index rebuilds

Occasionally, used space in temp tablespaces doesn't get released by Oracle (bug/quirk) so you need to manually drop a file from the tablespace, drop it from the file system and create another one.