Checking Tablespaces for Free Space Deficits

Another space management task that many DBAs routinely perform is to check which tablespaces contain objects whose next extent allocation request would exceed the current available contiguous free space. In other words, it checks which tablespaces are candidates for the infamous "unable to allocate extent" error message. The idea is to locate those tablespaces and allocate more space in order to avoid any such messages. For just this purpose, TOAD has the Identify Space Deficits screen shown in Figure 5.14, which is located on the main menu at DBA, Identify Space Deficits.

Figure 5.14. TOAD Identify Space Deficits screen.

So what exactly is the screen in Figure 5.14 pointing out? The USER_DICT tablespace contains four tables and three indexes, each of whose next extent allocation request size is larger than the tablespace's currently available free space. Let's assume that only the CITY table is likely to grow over time (that is, both STATE and COUNTRY tables are static and JUNK is only temporary). The space management problem is actually twice as bad as you may initially suspect. If the CITY table now adds rows that require an extent allocation, it's also quite likely that the CITY_IDX index will grow as well and thus also need more space. The point is that although this screen identifies space management problem areas, it does not really indicate just how big a problem you may have. So you should carefully review this screen's output before acting (that is, altering the tablespace to add a data file, increasing the size of a data file, or making a data file auto incremental in terms of its file size based upon need).