Tablespace allocated extents

Summary
The script below will show a map of allocated extents and free space in a given tablespace. Each line in the output is one extent or one chunk of free space. This script is useful to find out why your reports show that you have tons of room in your tablespace but you still fail to allocate the next extent, normally due to the lack of a large enough chunk of contiguous free space. Thinking about shrinking a datafile? Let this script show you how much room is at the end of that datafile and you know how much you can shrink that datafile. This script is also useful for reinforcing the notion of extents in the tablespace.
SELECT tablespace_name AS TABLESPACE, file_id, block_id, blocks,
       owner || '.' || segment_name AS SEGMENT
  FROM dba_extents
 WHERE tablespace_name = 'TOOLS'
UNION
SELECT tablespace_name AS TABLESPACE, file_id, block_id, blocks,
       '' AS SEGMENT
  FROM dba_free_space
 WHERE tablespace_name = 'TOOLS';
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