Answer by Andrew Mobbs ·
Possibly more usefully, understand how space is allocated in extents by Oracle. Each Segment (table, partition, index etc.) has one or more extents for storing data. The extents exist in a tablespace. When more storage is needed by a segment, another extent is allocated, the space in that extent may still mostly be unused, but now can only be used by that segment.
DBA_FREE_SPACE will tell you about the unallocated extents in a tablespace.
Use the procedures in DBMS_SPACE to query how much unused space has been allocated to an object.
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/schema005.htm provides a good overview
Answer by prodlife ·
Here's a query I use:
SELECT a.TABLESPACE_NAME, a.BYTES bytes_used, b.BYTES bytes_free, b.largest, round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used from ( select TABLESPACE_NAME, sum(BYTES) BYTES from dba_data_files group by TABLESPACE_NAME ) a left outer join ( select TABLESPACE_NAME, sum(BYTES) BYTES , max(BYTES) largest from dba_free_space group by TABLESPACE_NAME ) b on a.TABLESPACE_NAME=b.TABLESPACE_NAME order by ((a.BYTES-b.BYTES)/a.BYTES) desc