|Lesson 8|| Monitoring free space|
|Objective||List free space in a tablespace.|
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:
FROM dba_free_space fs,
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_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
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.