Tempfile usage
                                                 Last update (2008-02-21 11:36:02)
                                                                                                                   Date added (2008-02-12 15:08:25)

Summary
Some useful information about TEMP tablespace and how it is working.

How much free space has every tempfile
SELECT V$temp_space_header.tablespace_name, dba_temp_files.file_name, status, 
ROUND(bytes_used/(1024*1024),2) + ROUND(bytes_free/(1024*1024),2) MB_ALLOC,
ROUND(bytes_free/(1024*1024),2) "MB_FREE",
ROUND(bytes_used/(1024*1024),2) "MB_USED",
ROUND(((NVL(ROUND(bytes_free/(1024*1024),2),0))/(ROUND(bytes_used/(1024*1024),2) + 
ROUND(bytes_free/(1024*1024),2)))*100) pct_free,
dba_temp_files.AUTOEXTENSIBLE EXTENSIBLE
FROM V$temp_space_header, dba_temp_files
WHERE V$temp_space_header.file_id (+) = dba_temp_files.file_id
ORDER BY pct_free ASC;
Cashed temp segments
SELECT v$temp_extent_pool.tablespace_name, dba_temp_files.file_name,
bytes_cached/(1024*1024) CACHED_MB, bytes_used/(1024*1024) USED_MB
FROM v$temp_extent_pool, dba_temp_files
WHERE v$temp_extent_pool.file_id (+) = dba_temp_files.file_id;
Free extends for temp
SELECT tablespace_name, extent_size*:block_size/(1024*1024) extent_size_mb,
ROUND(total_extents*:block_size/(1024*1024),2) total_extents_mb,
ROUND(used_extents*:block_size/(1024*1024),2) currently_used_mb,
ROUND(free_extents*:block_size/(1024*1024),2) "FREE_MB(CACHED)",
ROUND(max_used_size*:block_size/(1024*1024),2) max_ever_used_mb
FROM v$sort_segment;
Explanation
block_size : for example 8192
extent_size : size of one extent, in number of Oracle blocks
total_extents: total number of extents in the segment (free or in use)
used_extents : total number of extents currently in use
free_extents : total number of extents currently marked as free!
max_used_size: maximum number of extents ever needed by an operation (like a sort):
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search