Monitoring "Temporary Tablespace" Usage in Oracle 9iR2 and 10g
By glennf on Jun 08, 2004
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