Monitoring "Temporary Tablespace" Usage in Oracle 9iR2 and 10g

This is useful for monitoring index builds and large DSS queries. The last thing you want to happen after a query has been running for a few hours is to run out of temporary space. Statspack will give you essentially the same information, but I like to have a simple little query for interactive monitoring.

SQL> select 100*(u.tot/d.tot) "pct_temp_used" FROM
     (select sum(u.blocks) tot from v$tempseg_usage u) u,
     (select sum(d.blocks) tot from dba_temp_files d) d
      /

pct_temp_used
-------------
        7.375