Monitoring free space in Oracle

Space is an important thing to watch in any database. Free space is important, because it represents space available to be allocated to objects. As tables, indexes, and other objects grow, they grow by having chunks of free space allocated to them. If your database doesn't have enough free space to accommodate this growth, you will run into problems.
Oracle organizes free space into extents. A free extent is a contiguous set of blocks, all within the same tablespace, that haven't yet been allocated to an object. The DBA_FREE_SPACE data dictionary view returns information about these free extents. A tablespace may have multiple free extents, so to find the total available space in a tablespace, you have to sum the sizes of all the free extents. Here's a query that does that:

SELECT t.tablespace_name, 

       SUM(bytes), SUM(blocks), 

       MAX(bytes), MAX(blocks)

FROM dba_free_space fs,

     dba_tablespaces t

WHERE t.tablespace_name = fs.tablespace_name(+)

GROUP BY t.tablespace_name

ORDER BY t.tablespace_name;

The output from this query will tell you the total amount of free space in each of your tablespaces. It also tells you the size of the largest single extent of free space in each tablespace. The outer join between DBA_FREE_SPACE and DBA_TABLESPACES ensures that all tablespaces are listed, even those with no free space at all. The output will look like this:

Tablespace        SUM(BYTES) SUM(BLOCKS) MAX(BYTES) MAX(BLOCKS)

----------------- ---------- ----------- ---------- -----------

SYSTEM              49827840       24330   49039360       23945

USERS

...

Here, the SYSTEM tablespace has 24,330 blocks of free space. The USERS tablespace, on the other hand, has no free space at all. Because DBA_FREE_SPACE did not show any free extents for USERS, the results for that tablespace are all null.
The size of the largest free extent is important because of the way in which Oracle allocates space. When it comes time to allocate space for an object, Oracle determines the size needed for the next extent of that object, and then looks for a free extent of at least that size. In other words, if you have a table where the next extent size is 10 megabytes, it won't do you any good to have two free extents of 5 megabytes each. You need a single free extent of 10 megabytes or greater. In the next lesson, you will learn how to find objects that fail to extend.