Examining Tablespace Usage and Projections

Probably the least disputed purely DBA job function is database space management, meaning the creation, monitoring, and overall administration of tablespaces and their associated data files. Even "do it all" consultants and senior developers who perform DBA tasks prefer to leave space management to someone else. One reason is that space has a tendency to reach some threshold or limit during normal sleeping hours, when batch jobs execute and pagers going off irritate spouses the most. Welcome to the world of production support!

Thus many DBAs performing database space management generally attempt to fulfill two goals. First, to avoid short-term space issues such as failure to allocate an extent for any permanent or temporary database object. And second, to possess sufficient disk space available for both short-term and mid- term database needs. It's this second objective that poses the greatest challenge, as anyone can pre-allocate all the storage in order to avoid "unable to allocate extent" messages. But it takes careful planning and attention to growth patterns in order to avoid running out of disk space before new disks arrive (or are even ordered).

Monitoring Tablespaces

TOAD offers two primary interfaces for space management: one for overall monitoring and another for administrative needs. Figure 5.7 shows the Tablespaces screen, which is used for overall monitoring and is located off the main menu at DBA, Tablespaces.

Figure 5.7. TOAD Tablespaces screen.

Note that the Tablespaces screen provides a single interface for viewing all your tablespace information at once. Note that each tab displays different detailed information across all the tablespaces in the database. However, it's the Space tab, shown in Figure 5.7, which is the most eye-appealing. It's the only place in TOAD where you can graphically view space usage across all your tablespaces at once.

Of special interest are the last two tabs on the Tablespaces screen: Space History and IO History. These tabs permit you to graphically display space and IO usage over time, assuming that this historical information is being collected. This is exactly that second and more critical space monitoring and planning step discussed earlier. Figure 5.8 shows the Space History tab.

Figure 5.8. TOAD Space History tab.

Note in Figure 5.8 that the QA database's dictionary-managed tablespace has grown very rapidly over the past month. Although it's only 22 megabytes now, it has grown from nothing to this size in less than a month. Look at the slope of the line. This tablespace is worth careful monitoring. But how big will it be next month, or better yet over the next three months? TOAD can predict this for you using sound statistical methods (assuming that you have sufficient history for TOAD to make an accurate projection). The rightmost icon in the upper left corner, which looks like a bar chart, will launch TOAD's Database/Tablespace Forecasting screen. Here you merely enter how far out to project and TOAD does the rest. Figure 5.9 shows that our QA database's dictionary-managed tablespace from Figure 5.8 will grow to nearly 105 megabytes over the next three months.

Figure 5.9. TOAD Database/Tablespace Forecasting screen.

When you open the Tablespaces screen, TOAD verifies whether your TOAD schema contains the necessary repository tables and data collection package, and whether it has been scheduled by using DBMS_JOBS. If the check fails and you are not connected as the TOAD user, you will see a warning that you must create these objects while connected as the TOAD user. But if the check fails and you are connected as the TOAD user , you will enter the Space Manager Setup screen, shown in Figure 5.10, to define and create all the required components (which you also can do manually when on either of these last two tabs by clicking the very first icon in the upper-left corner of the Tablespaces screen).

Figure 5.10. TOAD Space Manager Setup screen.

You have three very simple selections to make on this screen. First, how often should TOAD collect the information? Second, how long should TOAD keep that information? And third, should TOAD start with empty tables (that is, with no history) or should it try to project some data backwards ? The recommendation is to collect data every day (preferably just after a typical batch cycle), keep at least a year's worth of data (for more accurate projections), and to start with empty tables (no need for fake data being backfilled; it would just skew any short-term projections until sufficient history is collected).

Managing Tablespaces

TOAD's other primary interface for space management, which addresses more administrative needs, is the Schema Browser's Tablespaces tab, shown in Figure 5.11. The Schema Browser is launched by either selecting its icon on the main toolbar (second icon from the top left by default) or from the main menu at Database, Schema Browser. In fact, for many DBAs the Schema Browser may actually be their default initial screen, as defined in the TOAD Options for Startup.

Figure 5.11. TOAD Schema Browser Tablespaces tab.

The Schema Browser's Tablespaces tab provides much of the same detailed information about tablespaces and their data files as does the Tablespaces screen discussed in the preceding section, but more importantly, it provides numerous features for managing these resources as well. The left-hand side (LHS) of the Schema Browser's Tablespaces tab provides toolbar icons for the most common tablespace administrative tasks:

Create Script, which copies the selected tablespace's DDL to the Window's clipboard.
Create New Tablespace, which launches a modal window for creating a new tablespace.
Alter Tablespace, which launches a modal window for altering an existing tablespace.
Place Online, which alters the selected tablespaces to the online status.
Take Offline, which alters the selected tablespaces to the offline status and displays a red X next to the LHS display as shown in Figure 5.11 for the USER_DICT tablespace.
Show Tablespace Map, which launches the Tablespace Map screen for the selected tablespace (this screen is found on the main menu at DBA, Tablespace Map and is covered later in this chapter in the section "Checking the Level of Tablespace Fragmentation").
Export Tablespace Using Export Wizard, which launches the Export Utility Wizard (this wizard is found on the main menu at DBA, Export Utility Wizard).
Coalesce Tablespace, which coalesces the selected tablespaces in order to reduce fragmentation.
Drop Tablespace, which drops the selected tablespaces and their associated data files (where possible depending upon the Oracle version).

Moreover, the right-hand side (RHS) of the Schema Browser's Tablespaces tab provides toolbar icons for the Datafiles, Fragmentation, and Quotas tabs. The Datafiles tab toolbar icons are probably the most useful, and include:

Add Data File, which launches a modal window for creating a new data file.
Alter Data File, which launches a modal window for altering an existing data file ”and includes a handy utility for calculating the minimum size the data file can be resized down to, shown in Figure 5.12.

Figure 5.12. TOAD Datafile Definition window, for size estimation.

However, arguably the most useful aspect of the Schema Browser's Tablespaces tab is the multi-select capability, which enables you to select one or more tablespaces upon which to perform an administrative operation. For example, Figure 5.13 shows just how simple it now is to coalesce all the tablespaces in the entire database at once. You merely need to multi-select the desired tablespaces, right-click to activate the context menu, and then pick an operation. That's all there is to it.