How do I determine the free space in a tablespace?
asked Nov 10 '09 at 08:14 AM in Default
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
answered Nov 10 '09 at 11:31 AM
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
answered Nov 10 '09 at 04:42 PM