Oracle Database 12c DBA Handbook (2015)

Agood DBA has a toolset in place to monitor the database, both proactively monitoring various aspects of the database, such as transaction load, security enforcement, space management, and performance monitoring, and effectively reacting to any potentially disastrous system problems. Transaction management, performance tuning, memory management, and database security and auditing are covered in Chapters 7 through 10. In this chapter, we’ll address how a DBA can effectively and efficiently manage the disk space used by database objects in the different types of tablespaces: the SYSTEM tablespace, the SYSAUX tablespace, temporary tablespaces, undo tablespaces, and tablespaces of different sizes.

To reduce the amount of time it takes to manage disk space, it is important for the DBA not only to understand how the applications will be using the database, but also to provide guidance during the design of the database application. Designing and implementing the database application, including tablespace layouts and expected growth of the database, have been covered in Chapters 3, 4, and 5.

In this chapter, I’ll also provide some scripts that need not much more than SQL*Plus and the knowledge to interpret the results. These scripts are good for a quick look at the database’s health at a given point in time—for example, to see if there is enough disk space to handle a big SQL*Loader job that evening or to diagnose some response-time issues for queries that normally run quickly.

Oracle provides a number of built-in packages to help the busy DBA manage space and diagnose problems. For example, Oracle Segment Advisor, introduced in Oracle Database 10g, helps to determine if a database object has space available for reuse, given how much fragmentation exists in the object. Other features of Oracle, such as Resumable Space Allocation, allow a long-running operation that runs out of disk space to be suspended until the DBA can intervene and allocate enough additional disk space to complete the operation. As a result, the long-running job will not have to be restarted from the beginning.

We’ll also cover some of the key data dictionary and dynamic performance views that give us a close look at the structure of the database and a way to optimize space usage. Many of the scripts provided in this chapter use these views.

At the end of this chapter, we’ll cover two different methods for automating some of the scripts and Oracle tools: using the DBMS_SCHEDULER built-in package as well as using the Oracle Enterprise Manager (OEM) infrastructure.

Space usage for tablespaces will be the primary focus in this chapter, along with the objects contained within the tablespaces. Other database files, such as control files and redo log files, take up disk space, but as a percentage of the total space used by a database they are small. We will, however, briefly consider how archived log files are managed, because the number of archived log files will increase indefinitely at a pace proportional to how much DML activity occurs in the database. Therefore, a good plan for managing archived log files will help keep disk space usage under control.

Common Space Management Problems

Space management problems generally fall into one of three categories: running out of space in a regular tablespace, not having enough undo space for long-running queries that need a consistent “before” image of the tables, and insufficient space for temporary segments. Although we may still have some fragmentation issues within a database object such as a table or index, locally managed tablespaces solve the problem of tablespace fragmentation.

Each of these three problem areas are addressed by using the techniques described in the following sections.

Running Out of Free Space in a Tablespace

If a tablespace is not defined with the AUTOEXTEND attribute, then the total amount of space in all the datafiles that compose the tablespace limits the amount of data that can be stored in the tablespace. If the AUTOEXTEND attribute is defined, then one or more of the datafiles that compose the tablespace will grow to accommodate the requests for new segments or the growth of existing segments. Even with the AUTOEXTEND attribute, the amount of space in the tablespace is ultimately limited by the amount of disk space on the physical disk drive or storage group. If you have created a bigfile tablespace, you have only one datafile, but that single datafile has the same constraints as a datafile in a smallfile tablespace.

The AUTOEXTEND attribute is the default if you don’t specify the SIZE parameter in the CREATE TABLESPACE command and you are using Oracle Managed Files (OMF), so you’ll actually have to go out of your way to prevent a datafile from autoextending. In Oracle Database 11g or 12c, with the initialization parameter DB_CREATE_FILE_DEST set to an ASM or file system location, you can run a CREATE TABLESPACE command like this:

In this case, the tablespace BI_02 is created with the default initial extent size of 100MB in a single datafile, AUTOEXTEND is on, and the next extent is 100MB when the first datafile fills up. In addition, extent management is set to LOCAL, space allocation is AUTOALLOCATE, and segment space management is set to AUTO.

The conclusion to be reached here is that we want to monitor the free and used space within a tablespace to detect trends in space usage over time, and as a result be proactive in making sure that enough space is available for future space requests. You can use the DBMS_SERVER_ALERT package (with a PL/SQL call or via Cloud Control 12c) to automatically notify you when a tablespace reaches a warning or critical space threshold level, either at a percent used, space remaining, or both.

Insufficient Space for Temporary Segments

A temporary segment stores intermediate results for database operations such as sorts, index builds, DISTINCT queries, UNION queries, or any other operation that necessitates a sort/merge operation that cannot be performed in memory. Temporary segments should be allocated in a temporary tablespace, which was introduced in Chapter 1. Under no circumstances should the SYSTEM tablespace be used for temporary segments; when the database is created, a non-SYSTEM tablespace should be specified as a default temporary tablespace for users who are not otherwise assigned a temporary tablespace. If the SYSTEM tablespace is locally managed (which is preferred and the default since Oracle Database 10g), a default temporary tablespace must be defined when the database is created.

When there is not enough space available in the user’s default temporary tablespace, and either the tablespace cannot be autoextended or the tablespace’s AUTOEXTEND attribute is disabled, the user’s query or DML statement fails.

Too Much or Too Little Undo Space Allocated

Undo tablespaces have simplified the management of rollback information by managing undo information automatically within the tablespace. The DBA does not have to define the number and size of the rollback segments for the kinds of activity occurring in the database; as of Oracle 10g, manual rollback management has been deprecated.

Not only does an undo segment allow a rollback of an uncommitted transaction, it provides for read consistency of long-running queries that begin before INSERTs, UPDATEs, and DELETEs occur on a table. The amount of undo space available for providing read consistency is under the control of the DBA and is specified as the number of seconds that Oracle will attempt to guarantee that “before” image data is available for long-running queries.

As with temporary tablespaces, we want to make sure we have enough space allocated in an undo tablespace for peak demands without allocating more than is needed. As with any tablespace, we can use the AUTOEXTEND option when creating the tablespace to allow for unexpected growth of the tablespace without reserving too much disk space up front.

Undo segment management is discussed in detail in Chapter 7, whereas the tools to help size the undo tablespaces are discussed later in this chapter.

Fragmented Tablespaces and Segments

A tablespace that is locally managed uses bitmaps to keep track of free space, which, in addition to eliminating the contention on the data dictionary, eliminates wasted space because all extents are either the same size (with uniform extent allocation) or are multiples of the smallest size (with autoallocation). For migrating from a dictionary-managed tablespace, we will review an example that converts a dictionary-managed tablespace to a locally managed tablespace. In a default installation of Oracle Database 10g or later using the Database Configuration Assistant (DBCA), all tablespaces, including the SYSTEM and SYSAUX tablespaces, are created as locally managed tablespaces.

By default, starting with Oracle Database 11g, you don’t need to specify many options in the CREATE TABLESPACE statement to get a locally managed tablespace with Automatic Segment Space Management (ASSM):

Only the UNIFORM clause is required if you want fixed extent sizes; otherwise, the default is AUTOALLOCATE and therefore Oracle manages the extent sizes.

This tablespace will be created with an initial size of 250MB, and it can grow as large as 2000MB (2GB); extents will be locally managed with a bitmap, and every extent in this tablespace will be exactly 8MB in size. Space within each segment (table or index) will be managed automatically with a bitmap instead of freelists.

Even with efficient extent allocation, table and index segments may eventually contain a lot of free space due to UPDATE and DELETE statements. As a result, a lot of unused space can be reclaimed by using some of the scripts provided later in this chapter, as well as by using Oracle Segment Advisor.

Oracle Segments, Extents, and Blocks

Chapter 1 provided an overview of tablespaces and the logical structures contained within them. Also briefly presented were datafiles, allocated at the operating system level, as the building blocks for tablespaces. Being able to effectively manage disk space in the database requires an in-depth knowledge of tablespaces and datafiles, as well as the components of the segments stored within the tablespaces, such as tables and indexes. At the lowest level, a tablespace segment consists of one or more extents, each extent comprising one or more data blocks. Figure 6-1 shows the relationship between segments, extents, and blocks in an Oracle database.

FIGURE 6-1. Oracle segments, extents, and blocks

In the following sections are the details of data blocks, extents, and segments with the focus on space management.

Data Blocks

A data block is the smallest unit of storage in the database. Ideally, an Oracle block is a multiple of the operating system block to ensure efficient I/O operations. The default block size for the database is specified with the DB_BLOCK_SIZE initialization parameter; this block size is used for the SYSTEM, TEMP, and SYSAUX tablespaces at database creation and cannot be changed without re-creating the database.

The format for a data block is presented in Figure 6-2.

FIGURE 6-2. Contents of an Oracle data block

Every data block contains a header that specifies what kind of data is in the block: table rows or index entries. The table directory section has information about the table with rows in the block; a block can have rows from only one table or entries from only one index, unless the table is a clustered table, in which case the table directory identifies all the tables with rows in this block. The row directory provides details of the specific rows of the table or index entries in the block.

The space for the header, table directory, and row directory is a very small percentage of the space allocated for a block; our focus, then, is on the free space and row data within the block.

Within a newly allocated block, free space is available for new rows and updates to existing rows; the updates may increase or decrease the space allocated for the row if there are varying-length columns in the row or a non-NULL value is changed to a NULL value, or vice versa. Space is available within a block for new inserts until there is less than a certain percentage of space available in the block defined by the PCTFREE parameter, specified when the segment is created. Once there is less than PCTFREE space in the block, no inserts are allowed. If freelists are used to manage space within the blocks of a segment, then new inserts are allowed on the table when used space within the block falls below PCTUSED.

A row may span more than one block if the row size is greater than the block size or an updated row no longer fits into the original block. In the first case, a row that is too big for a block is stored in a chain of blocks; this may be unavoidable if a row contains columns that exceed even the largest block size allowed, which in Oracle 11g is 32KB.

In the second case, an update to a row in a block may no longer fit in the original block, and as a result Oracle will migrate the data for the entire row to a new block and leave a pointer in the first block to point to the location in the second block where the updated row is stored. As you may infer, a segment with many migrated rows may cause I/O performance problems because the number of blocks required to satisfy a query can double. In some cases, adjusting the value of PCTFREE or rebuilding the table may result in better space utilization and I/O performance. More tips on how to improve I/O performance can be found in Chapter 8.

Starting with Oracle9i Release 2, you can use Automatic Segment Space Management (ASSM) to manage free space within blocks; you enable ASSM in locally managed tablespaces by using the SEGMENT SPACE MANAGEMENT AUTO keywords in the CREATE TABLESPACE command (although this is the default for locally managed tablespaces).

Using ASSM reduces segment header contention and improves simultaneous insert concurrency; this is because the free space map in a segment is spread out into a bitmap block within each extent of the segment. As a result, you dramatically reduce waits because each process performing INSERT, UPDATE, or DELETE operations will likely be accessing different blocks instead of one freelist or one of a few freelist groups. In addition, each extent’s bitmap block lists each block within the extent along with a four-bit “fullness” indicator defined as follows (with room for future expansion from values 6-15):

0000 Unformatted block

0001 Block full

0010 Less than 25 percent free space available

0011 25 percent to 50 percent free space

0100 50 percent to 75 percent free space

0101 Greater than 75 percent free space

In a RAC database environment, using ASSM segments means you no longer need to create multiple freelist groups. In addition, you no longer need to specify PCTUSED, FREELISTS, or FREELIST GROUPS parameters when you create a table; if you specify any of these parameters, they are ignored.

Extents

An extent is the next level of logical space allocation in a database; it is a specific number of blocks allocated for a specific type of object, such as a table or index. An extent is the minimum number of blocks allocated at one time; when the space in an extent is full, another extent is allocated.

When a table is created, an initial extent is allocated. Once the space is used in the initial extent, incremental extents are allocated. In a locally managed tablespace, these subsequent extents can either be the same size (using the UNIFORM keyword when the tablespace is created) or optimally sized by Oracle (AUTOALLOCATE). For extents that are optimally sized, Oracle starts with a minimum extent size of 64KB and increases the size of subsequent extents as multiples of the initial extent as the segment grows. In this way, fragmentation of the tablespace is virtually eliminated.

When the extents are sized automatically by Oracle, the storage parameters INITIAL, NEXT, and MINEXTENTS are used as a guideline, along with Oracle’s internal algorithm, to determine the best extent sizes. In the following example, a table created in the USERS tablespace (during installation of a new database, the USERS tablespace is created with AUTOALLOCATE enabled) does not use the storage parameters specified in the CREATE TABLE statement:

Unless a table is truncated or the table is dropped, any blocks allocated to an extent remain allocated for the table, even if all rows have been deleted from the table. The maximum number of blocks ever allocated for a table is known as the high-water mark (HWM).

Segments

Groups of extents are allocated for a single segment. A segment must be wholly contained within one and only one tablespace. Every segment represents one and only one type of database object, such as a table, a partition of a partitioned table, an index, or a temporary segment. For partitioned tables, every partition resides in its own segment; however, a cluster (with two or more tables) resides within a single segment. Similarly, a partitioned index consists of one segment for each index partition.

Temporary segments are allocated in a number of scenarios. When a sort operation cannot fit in memory, such as a SELECT statement that needs to sort the data to perform a DISTINCT, GROUP BY, or UNION operation, a temporary segment is allocated to hold the intermediate results of the sort. Index creation also typically requires the creation of a temporary segment. Because allocation and deallocation of temporary segments occur often, it is highly desirable to create a tablespace specifically to hold temporary segments. This helps to distribute the I/O required for a given operation, and it reduces the possibility that fragmentation may occur in other tablespaces due to the allocation and deallocation of temporary segments. When the database is created, a default temporary tablespace can be created for any new users who do not have a specific temporary tablespace assigned; if the SYSTEM tablespace is locally managed (which it should be for any new database), a separate temporary tablespace must be created to hold temporary segments.

How space is managed within a segment depends on how the tablespace containing the block is created. If the tablespace is locally managed (the default and recommended), space in segments can be managed with either freelists or bitmaps. Oracle strongly recommends that all new tablespaces be created as locally managed and that free space within segments be managed automatically with bitmaps. Automatic Segment Space Management allows more concurrent access to the bitmap lists in a segment compared to freelists; in addition, tables that have widely varying row sizes make more efficient use of space in segments that are automatically managed.

As mentioned earlier, in the section titled “Data Blocks,” if a segment is created with Automatic Segment Space Management, bitmaps are used to manage the space within the segment. As a result, the PCTUSED, FREELIST, and FREELIST GROUPS keywords within a CREATE TABLE or CREATE INDEX statement are ignored. The three-level bitmap structure within the segment indicates whether blocks below the HWM are full (less than PCTFREE), 0 to 25 percent free, 25 to 50 percent free, 50 to 75 percent free, 75 to 100 percent free, or unformatted.

Data Dictionary Views and Dynamic Performance Views

A number of data dictionary views and dynamic performance views are critical in understanding how disk space is being used in your database. The data dictionary views that begin with DBA_ are of a more static nature, whereas the V$ views, as expected, are of a more dynamic nature and give you up-to-date statistics on how space is being used in the database.

In the next few sections, I’ll highlight the space management views and provide some quick examples; later in this chapter, you’ll see how these views form the basis of Oracle’s space management tools.

DBA_TABLESPACES

The view DBA_TABLESPACES contains one row for each tablespace, whether native or currently plugged in from another database. It contains default extent parameters for objects created in the tablespace that don’t specify INITIAL and NEXT values. The EXTENT_MANAGEMENT column indicates whether the tablespace is locally managed or dictionary managed. As of Oracle 10g, the column BIGFILE indicates whether the tablespace is a smallfile or a bigfile tablespace. Bigfile tablespaces are discussed later in this chapter.

In the following query we retrieve the tablespace type and the extent management type for all tablespaces within the database:

In this example, all the tablespaces are locally managed; in addition, the DMARTS tablespace has a larger block size to improve response time for data mart tables that are typically accessed hundreds or thousands of rows at a time.

DBA_SEGMENTS

The data dictionary view DBA_SEGMENTS has one row for each segment in the database. This view is not only good for retrieving the size of the segment, in blocks or bytes, but also for identifying the owner of the object and the tablespace where an object resides:

DBA_EXTENTS

The DBA_EXTENTS view is similar to DBA_SEGMENTS, except that DBA_EXTENTS drills down further into each database object. There is one row in DBA_EXTENTS for each extent of each segment in the database, along with the FILE_ID and BLOCK_ID of the datafile containing the extent:

In this example, the table AUD$ owned by SYS has extents in two different datafiles that compose the SYSTEM tablespace.

DBA_FREE_SPACE

The view DBA_FREE_SPACE is broken down by datafile number within the tablespace. You can easily compute the amount of free space in each tablespace by using the following query:

Note that the free space does not take into account the space that would be available if and when the datafiles in a tablespace are autoextended. Also, any space allocated to a table for rows that are later deleted will be available for future inserts into the table, but it is not counted in the preceding query results as space available for other database objects. When a table is truncated, however, the space is made available for other database objects.

DBA_LMT_FREE_SPACE

The view DBA_LMT_FREE_SPACE provides the amount of free space, in blocks, for all tablespaces that are locally managed, and it must be joined with DBA_DATA_FILES to get the tablespace names.

DBA_THRESHOLDS

Introduced in Oracle Database 10g, DBA_THRESHOLDS contains the currently active list of the different metrics that gauge the database’s health and specify a condition under which an alert will be issued if the metric threshold reaches or exceeds a specified value.

The values in this view are typically maintained via the OEM interface; in addition, the DBMS_SERVER_ALERT built-in PL/SQL package can set and get the threshold values with the SET_THRESHOLD and GET_THRESHOLD procedures, respectively. To read alert messages in the alert queue, you can use the DBMS_AQ and DBMS_AQADM packages, or OEM can be configured to send a pager or e-mail message when the thresholds have been exceeded.

For a default installation of Oracle Database 12c, a number of thresholds are configured, including the following:

At least one user session is blocked every minute for three consecutive minutes.

Any segments are not able to extend for any reason.

The total number of concurrent processes comes within 80 percent of the PROCESSES initialization parameter value.

More than two invalid objects exist for any individual database user.

The total number of concurrent user sessions comes within 80 percent of the SESSIONS initialization parameter value.

There are more than 1200 concurrent open cursors.

There are more than 100 logons per second.

A tablespace is more than 85 percent full (warning) or more than 97 percent full (critical).

User logon time is greater than 1000 milliseconds (1 second).

DBA_OUTSTANDING_ALERTS

The data dictionary view DBA_OUTSTANDING_ALERTS contains one row for each active alert in the database, until the alert is cleared or reset. One of the fields in this view, SUGGESTED_ACTION, contains a recommendation for addressing the alert condition.

DBA_OBJECT_USAGE

If an index is not being used, it not only takes up space that could be used by other objects, but the overhead of maintaining the index whenever an INSERT, UPDATE, or DELETE occurs is wasted. By using the ALTER INDEX … MONITORING USAGE command, the data dictionary view DBA_OBJECT_USAGE will be updated when the index has been accessed indirectly because of a SELECT statement.

NOTE

V$OBJECT_USAGE is deprecated as of Oracle Database 12c and is retained for backward compatibility; use DBA_OBJECT_USAGE or USER_OBJECT_USAGE instead.

DBA_ALERT_HISTORY

After an alert in DBA_OUTSTANDING_ALERTS has been addressed and cleared, a record of the cleared alert is available in the view DBA_ALERT_HISTORY.

V$ALERT_TYPES

The dynamic performance view V$ALERT_TYPES contains the 175 alert conditions (as of Oracle 12c, Release 1) that can be monitored. The GROUP_NAME column categorizes the alert conditions by type. For example, for space management issues, we would use alerts with a GROUP_NAME of “Space”:

Using the alert with REASON_ID=123 as an example, an alert can be initiated when the free space in the database recovery area falls below a specified percentage.

V$UNDOSTAT

Having too much undo space and having not enough undo space are both problems. Although an alert can be set up to notify the DBA when the undo space is not sufficient to provide enough transaction history to satisfy Flashback queries or enough “before” image data to prevent “Snapshot Too Old” errors, a DBA can be proactive by monitoring the dynamic performance view V$UNDOSTAT during heavy database usage periods.

V$UNDOSTAT displays historical information about the consumption of undo space for ten-minute intervals. By analyzing the results from this table, a DBA can make informed decisions when adjusting the size of the undo tablespace or changing the value of the UNDO_RETENTION initialization parameter.

V$SORT_SEGMENT

The view V$SORT_SEGMENT can be used to view the allocation and deallocation of space in a temporary tablespace’s sort segment. The column CURRENT_USERS indicates how many distinct users are actively using a given segment. V$SORT_SEGMENT is only populated for temporary tablespaces.

V$TEMPSEG_USAGE

From the perspective of users requesting temporary segments, the view V$TEMPSEG_USAGE identifies the locations, types, and sizes of the temporary segments currently being requested. Unlike V$SORT_SEGMENT, V$TEMPSEG_USAGE will contain information about temporary segments in both temporary and permanent tablespaces. Later in this chapter, I’ll introduce the improved and simplified temporary tablespace management tools available since Oracle Database 11g.

Space Management Methodologies

In the following sections, we will consider various features of Oracle 12c to facilitate the efficient use of disk space in the database. Locally managed tablespaces offer a variety of advantages to the DBA, improving the performance of the objects within the tablespace, as well as easing administration of the tablespace. Fragmentation of a tablespace is a thing of the past. Another feature, Oracle Managed Files, eases datafile maintenance by automatically removing files at the operating system level when a tablespace or other database object is dropped. Bigfile tablespaces, introduced in Oracle 10g, simplify datafile management because one (and only one) datafile is associated with a bigfile tablespace. This moves the maintenance point up one level from the datafile to the tablespace. We’ll also review a couple other features introduced in earlier releases: undo tablespaces and multiple block sizes.

Locally Managed Tablespaces

Prior to Oracle8i, there was only one way to manage free space within a tablespace: by using data dictionary tables in the SYSTEM tablespace. If a lot of INSERT, DELETE, and UPDATE activity occurred anywhere in the database, there was the potential for a “hot spot” to occur in the SYSTEM tablespace where the space management occurred. Oracle removed this potential bottleneck by introducing locally managed tablespaces (LMTs). A locally managed tablespace tracks free space in the tablespace with bitmaps, as discussed in Chapter 1. These bitmaps can be managed very efficiently because they are very compact compared to a freelist of available blocks. Because they are stored within the tablespace itself, instead of in the data dictionary tables, contention in the SYSTEM tablespace is reduced.

Since Oracle Database 10g, by default, all tablespaces are created as locally managed tablespaces, including the SYSTEM and SYSAUX tablespaces. When the SYSTEM tablespace is locally managed, you can no longer create any dictionary-managed tablespaces in the database that are read/write. A dictionary-managed tablespace may still be plugged into the database from an earlier version of Oracle, but it is read-only.

An LMT can have objects with one of two types of extents: automatically sized or all of a uniform size. If extent allocation is set to UNIFORM when the LMT is created, all extents, as expected, are the same size. Because all extents are the same size, there can be no fragmentation. Gone is the classic example of a 51MB segment that can’t be allocated in a tablespace with two free 50MB extents because the two 50MB extents are not adjacent.

On the other hand, automatic segment extent management within a locally managed tablespace allocates space based on the size of the object. Initial extents are small, and if the object stays small, very little space is wasted. If the table grows past the initial extent allocated for the segment, subsequent extents to the segment are larger. Extents in an autoallocated LMT have sizes of 64KB, 1MB, 8MB, and 64MB, and the extent size increases as the size of the segment increases, up to a maximum of 64MB. In other words, Oracle is specifying what the values of INITIAL, NEXT, and PCTINCREASE are automatically, depending on how the object grows. Although it seems like fragmentation can occur in a tablespace with autoallocation, in practice the fragmentation is minimal because a new object with a 64KB initial segment size will fit nicely in a 1MB, 4MB, 8MB, or 64MB block preallocated for all other objects with an initial 64KB extent size.

Given an LMT with either automatically managed extents or uniform extents, the free space within the segment itself can be AUTO or MANUAL. With AUTO segment space management, a bitmap is used to indicate how much space is used in each block. As mentioned earlier in the chapter, the parameters PCTUSED, FREELISTS, and FREELIST GROUPS no longer need to be specified when the segment is created. In addition, the performance of concurrent DML operations is improved because the segment’s bitmap allows concurrent access. In a freelist-managed segment, the data block in the segment header that contains the freelist is locked out to all other writers of the block when a single writer is looking for a free block in the segment. Although allocating multiple freelists for very active segments does somewhat solve the problem, it is another structure that the DBA has to manage.

Another advantage of LMTs is that rollback information is reduced or eliminated when any LMT space-related operation is performed. Because the update of a bitmap in a tablespace is not recorded in a data dictionary table, no rollback information is generated for this transaction.

Other than third-party applications, such as older versions of SAP that require dictionary-managed tablespaces, there are no other reasons for creating new dictionary-managed tablespaces in Oracle 12c. As mentioned earlier, compatibility is provided in part to allow dictionary-managed tablespaces from previous versions of Oracle to be “plugged into” an Oracle 12c database (as a transportable tablespace). An Oracle 11g database as a whole can be “plugged into” a container database (CDB) in a multitenant architecture. But if the SYSTEM tablespace is locally managed, any dictionary-managed tablespaces must be opened read-only. Later in this chapter, you’ll see some examples where we can optimize space and performance by moving a tablespace from one database to another and allocating additional data buffers for tablespaces with different sizes.

Migrating a dictionary-managed tablespace to a locally managed tablespace is very straightforward using the DBMS_SPACE_ADMIN built-in package:

If you’re upgrading your database from Oracle 11g to 12c, you must also convert the SYSTEM tablespace to an LMT; if so, a number of prerequisites are in order:

Before starting the migration, shut down the database and perform a cold backup of the database.

Any non-SYSTEM tablespaces that are to remain read/write should be converted to LMTs.

The default temporary tablespace must not be SYSTEM.

If automatic undo management is being used, the undo tablespace must be online.

For the duration of the conversion, all tablespaces except for the undo tablespace must be set to read-only.

The database must be started in RESTRICTED mode for the duration of the conversion.

If any of these conditions are not met, the TABLESPACE_MIGRATE_TO_LOCAL procedure will not perform the migration.

Using OMF to Manage Space

In a nutshell, Oracle-Managed Files (OMF) simplifies the administration of an Oracle database. At database creation time, or later by changing a couple parameters in the initialization parameter file, the DBA can specify a number of default locations for database objects such as datafiles, redo log files, and control files. Prior to Oracle9i, the DBA had to remember where the existing datafiles were stored by querying the DBA_DATA_FILES and DBA_TEMP_FILES views. On many occasions, a DBA would drop a tablespace but would forget to delete the underlying datafiles, thus wasting space and the time it took to back up files that were no longer used by the database.

Using OMF, Oracle not only automatically creates and deletes the files in the specified directory location but also ensures that each filename is unique. This avoids corruption and database downtime in a non-OMF environment due to existing files being overwritten by a DBA inadvertently creating a new datafile with the same name as an existing datafile, and using the REUSE clause. OMF fits in nicely with Automatic Storage Management (ASM) to make datafile creation as simple as specifying +DATA as the destination for the datafile to reside. ASM will automatically put the datafile into an ASM subdirectory divided by database name and object type:

If you set the initialization parameter DB_FILE_CREATE_DEST to +DATA, you don’t even have to specify the disk group +DATA in the CREATE TABLESPACE command.

In a test or development environment, OMF reduces the amount of time the DBA must spend on file management and lets him or her focus on the applications and other aspects of the test database. OMF has an added benefit for packaged Oracle applications that need to create tablespaces: The scripts that create the new tablespaces do not need any modification to include a datafile name, thus increasing the likelihood of a successful application deployment.

Migrating to OMF from a non-OMF environment is easy, and it can be accomplished over a longer time period. Non-OMF files and OMF files can coexist indefinitely in the same database. When the appropriate initialization parameters are set, all new datafiles, control files, and redo log files can be created as OMF files, while the previously existing files can continue to be managed manually until they are converted to OMF, if ever.

The OMF-related initialization parameters are detailed in Table 6-1. Note that the operating system path specified for any of these initialization parameters must already exist; Oracle will not create the directory. Also, these directories must be writable by the operating system account that owns the Oracle software (which on most platforms is oracle).

TABLE 6-1. OMF-Related Initialization Parameters

Bigfile Tablespaces

Bigfile tablespaces, introduced in Oracle 10g, take OMF files to the next level. In a bigfile tablespace, a single datafile is allocated, and it can be up to 8EB (exabytes, a million terabytes) in size.

Bigfile tablespaces can only be locally managed with Automatic Segment Space Management. If a bigfile tablespace is used for automatic undo or for temporary segments, then segment space management must be set to MANUAL.

Bigfile tablespaces can save space in the System Global Area (SGA) and the control file because fewer datafiles need to be tracked; similarly, all ALTER TABLESPACE commands on bigfile tablespaces need not refer to datafiles because one and only one datafile is associated with each bigfile tablespace. This moves the maintenance point from the physical (datafile) level to the logical (tablespace) level, simplifying administration. One downside to bigfile tablespaces is that a backup of a bigfile tablespace uses a single process; a number of smaller tablespaces, however, can be backed up using parallel processes and will most likely take less time to back up than a single bigfile tablespace.

Bigfile tablespaces can be backed up using multiple processes by setting SECTION SIZE when using RMAN. For example, RMAN level 0 backups that are scripted to include the SECTION SIZE 64G parameter to allow parallel backups of big file tablespaces. Oracle will automatically use a single process for any file smaller than 64GB.

Creating a bigfile tablespace is as easy as adding the BIGFILE keyword to the CREATE TABLESPACE command:

If you are using OMF, then the DATAFILE clause can be omitted. To resize a bigfile tablespace, you can use the RESIZE clause:

In this scenario, even 80GB is not big enough for this tablespace, so we will let it autoextend 20GB at a time:

Notice in both cases that we do not need to refer to a datafile; there is only one datafile, and once the tablespace is created, we no longer need to worry about the details of the underlying datafile and how it is managed.

Bigfile tablespaces are intended for use with Automatic Storage Management, discussed in the next section.

Automatic Storage Management

Using Automatic Storage Management (ASM) can significantly reduce the administrative overhead of managing space in a database because a DBA need only specify an ASM disk group when allocating space for a tablespace or other database object. Database files are automatically distributed among all available disks in a disk group, and the distribution is automatically updated whenever the disk configuration changes. For example, when a new disk volume is added to an existing disk group in an ASM instance, all datafiles within the disk group are redistributed to use the new disk volume. ASM was introduced in Chapter 4. In this section, we’ll revisit some other key ASM concepts from a storage management point of view and provide more examples.

Because ASM automatically places datafiles on multiple disks, performance of queries and DML statements is improved because the I/O is spread out among several disks. Optionally, the disks in an ASM group can be mirrored to provide additional redundancy and performance benefits.

Using ASM provides a number of other benefits. In many cases, an ASM instance with a number of physical disks can be used instead of a third-party volume manager or network-attached storage (NAS) subsystem. As an added benefit over volume managers, ASM maintenance operations do not require a shutdown of the database if a disk needs to be added or removed from a disk group.

In the next few sections, we’ll delve further into how ASM works, with an example of how to create a database object using ASM.

Disk Group Redundancy

A disk group in ASM is a collection of one or more ASM disks managed as a single entity. Disks can be added or removed from a disk group without shutting down the database. Whenever a disk is added or removed, ASM automatically rebalances the datafiles on the disks to maximize redundancy and I/O performance.

In addition to the advantages of high redundancy, a disk group can be used by more than one database. This helps to maximize the investment in physical disk drives by easily reallocating disk space among several databases whose disk space needs may change over the course of a day or the course of a year.

As explained in Chapter 4, the three types of disk groups are normal redundancy, high redundancy, and external redundancy. The normal-redundancy and high-redundancy groups require that ASM provide the redundancy for files stored in the group. The difference between normal redundancy and high redundancy is in the number of failure groups required: A normal-redundancy disk group typically has two failure groups, and a high-redundancy disk group will have at least three failure groups. A failure group in ASM would roughly correspond to a redo log file group member using traditional Oracle datafile management. External redundancy requires that the redundancy be provided by a mechanism other than ASM (for example, with a hardware third-party RAID storage array). Alternatively, a disk group might contain a non-mirrored disk volume that is used for a read-only tablespace that can easily be re-created if the disk volume fails.

ASM Instance

ASM requires a dedicated Oracle instance, typically on the same node as the database that is using an ASM disk group. In an Oracle Real Application Clusters (RAC) environment, each node in a RAC database has an ASM instance. In Oracle Database 12c, an Oracle Flex ASM instance can reside on a physical server that does not host a database instance.

An ASM instance never mounts a database; it only coordinates the disk volumes for other database instances. In addition, all database I/O from an instance goes directly to the disks in a disk group. Disk group maintenance, however, is performed in the ASM instance; as a result, the memory footprint needed to support an ASM instance can be as low as 275MB but is typically at least 2GB in a production environment.

For more details on how to configure ASM or Oracle Flex ASM for use with RAC, see Chapter 12.

Background Processes

Several Oracle background processes exist in an ASM instance. The RBAL background process coordinates the automatic disk group rebalance activity for a disk group. Other ASM background processes, ARB0 through ARB9 and ARBA, perform the actual rebalance activity in parallel. When ASM transactions terminate abnormally, the ASM processes ARSn (where n is a number from 0 to 9) perform the recovery.

Creating Objects Using ASM

Before a database can use an ASM disk group, the group must be created by the ASM instance. In the following example, a new disk group, LYUP25, is created to manage the Unix disk volumes /dev/hda1, /dev/hda2, /dev/hda3, /dev/hdb1, /dev/hdc1, and /dev/hdd4:

When normal redundancy is specified, at least two failure groups must be specified to provide two-way mirroring for any datafiles created in the disk group.

In the database instance that is using the disk group, OMF is used in conjunction with ASM to create the datafiles for the logical database structures. In the following example, we set the initialization parameter DB_CREATE_FILE_DEST using a disk group so that any tablespaces created using OMF will automatically be named and placed in the disk group LYUP25:

Creating a tablespace in the disk group is straight to the point:

Once an ASM file is created, the automatically generated filenames can be found in V$DATAFILE and V$LOGFILE, along with manually generated filenames. All typical database files can be created using ASM, except for administrative files, including trace files, alert logs, backup files, export files, and core dump files.

OMF is a handy option when you want to let Oracle manage the datafile naming for you, whether the datafile is on a conventional file system or in an ASM disk group. You can also mix and match: some of your datafiles can be OMF-named, and others explicitly named.

Undo Management Considerations

Creating an undo tablespace provides a number of benefits for both the DBA and a typical database user. For the DBA, the management of rollback segments is a thing of the past (the past century!): all undo segments are managed automatically by Oracle in the undo tablespace. In addition to providing a read-consistent view of database objects to database readers when a long transaction against an object is in progress, an undo tablespace can provide a mechanism for a user to recover rows from a table.

A big enough undo tablespace will minimize the possibility of getting the classic “Snapshot too old” error message, but how much undo space is enough? If it is undersized, then the availability window for flashback queries is short; if it is sized too big, disk space is wasted and backup operations may take longer than necessary.

A number of initialization parameter files control the allocation and use of undo tablespaces. The UNDO_MANAGEMENT parameter specifies whether AUTOMATIC undo management is used, and the UNDO_TABLESPACE parameter specifies the undo tablespace itself. To change undo management from rollback segments to automatic undo management (changing the value of UNDO_MANAGEMENT from MANUAL to AUTO), the instance must be shut down and restarted for the change to take effect; you can change the value of UNDO_TABLESPACE while the database is open. The UNDO_RETENTION parameter specifies, in seconds, the minimum amount of time that undo information should be retained for Flashback queries. However, with an undersized undo tablespace and heavy DML usage, some undo information may be overwritten before the time period specified in UNDO_RETENTION.

Introduced in Oracle Database 10g was the RETENTION GUARANTEE clause of the CREATE UNDO TABLESPACE command. In essence, an undo tablespace with a RETENTION GUARANTEE will not overwrite unexpired undo information at the expense of failed DML operations when there is not enough free undo space in the undo tablespace. More details on using this clause can be found in Chapter 7.

The following initialization parameters enable automatic undo management with the undo tablespace UNDO04 using a retention period of at least 24 hours:

The dynamic performance view V$UNDOSTAT can assist in sizing the undo tablespace correctly for the transaction load during peak processing periods. The rows in V$UNDOSTAT are inserted at ten-minute intervals and give a snapshot of the undo tablespace usage:

In this example, a peak in undo space usage occurred between 9:41 A.M. and 9:51 A.M., resulting in a “Snapshot too old” error for three queries. To prevent these errors, the undo tablespace should be either manually resized or allowed to autoextend.

SYSAUX Monitoring and Usage

The SYSAUX tablespace, introduced in Oracle 10g, is an auxiliary tablespace to the SYSTEM tablespace, and it houses data for several components of the Oracle database that either required their own tablespace or used the SYSTEM tablespace in previous releases of Oracle. These components include the Enterprise Manager Repository, formerly in the tablespace OEM_REPOSITORY, as well as LogMiner, Oracle Spatial, and Oracle Text, all of which formerly used the SYSTEM tablespace for storing configuration information. The current occupants of the SYSAUX tablespace can be identified by querying the V$SYSAUX_OCCUPANTS view:

If the SYSAUX tablespace is taken offline or otherwise becomes corrupted, only these components of the Oracle database will be unavailable; the core functionality of the database will be unaffected. In any case, the SYSAUX tablespace helps to take the load off of the SYSTEM tablespace during normal operation of the database.

To monitor the usage of the SYSAUX tablespace, you can query the column SPACE_USAGE_KBYTES on a routine basis, and it can alert the DBA when the space usage grows beyond a certain level. If the space usage for a particular component requires a dedicated tablespace to be allocated for the component, such as for the Oracle Text repository, the procedure identified in the MOVE_PROCEDURE column of the V$SYSAUX_OCCUPANTS view will move the application to another tablespace:

If a component is not being used in the database at all, such as for TSM or Ultra Search, a negligible amount of space is used in the SYSAUX tablespace.

Archived Redo Log File Management

It is important to consider space management for objects that exist outside of the database, such as archived redo log files. In ARCHIVELOG mode, an online redo log file is copied to the destination(s) specified by LOG_ARCHIVE_DEST_n (where n is a number from 1 to 10) or by DB_RECOVERY_FILE_DEST (the flash recovery area) if none of the LOG_ARCHIVE_DEST_n values are set.

The redo log being copied must be copied successfully to at least one of the destinations before it can be reused by the database. The LOG_ARCHIVE_MIN_SUCCEED_DEST parameter defaults to 1 and must be at least 1. If none of the copy operations are successful, the database will be suspended until at least one of the destinations receives the log file. Running out of disk space is one possible reason for this type of failure.

If the destination for the archived log files is on a local file system, an operating system shell script can monitor the space usage of the destination, or it can be scheduled with DBMS_SCHEDULER or with Oracle Cloud Control 12c.

Built-in Space Management Tools

Oracle 12c provides a number of built-in tools that a DBA can use on demand to determine if there are any problems with disk space in the database. Most, if not all, of these tools can be manually configured and run by calling the appropriate built-in package. In this section, we’ll cover the packages and procedures used to query the database for space problems or advice on space management. In addition, I’ll show you the new initialization parameter used by the Automatic Diagnostic Repository to identify the alert and trace file location. Later in this chapter, you’ll see how some of these tools can be automated to notify the DBA via e-mail or pager when a problem is imminent; many, if not all, of these tools are available on demand via the Oracle Cloud Control 12c web interface.

Segment Advisor

Frequent inserts, updates, and deletes on a table may, over time, leave the space within a table fragmented. Oracle can perform segment shrink on a table or index. Shrinking the segment makes the free space in the segment available to other segments in the tablespace, with the potential to improve future DML operations on the segment because fewer blocks may need to be retrieved for the DML operation after the segment shrink. Segment shrink is very similar to online table redefinition in that space in a table is reclaimed. However, segment shrink can be performed in place without the additional space requirements of online table redefinition.

To determine which segments will benefit from segment shrink, you can invoke Segment Advisor to perform growth trend analysis on specified segments. In this section, we’ll invoke Segment Advisor on some candidate segments that may be vulnerable to fragmentation.

In the example that follows, we’ll set up Segment Advisor to monitor the HR.EMPLOYEES table. In recent months, there has been high activity on this table; in addition, a new column, WORK_RECORD, has been added to the table, which HR uses to maintain comments about the employees:

We have enabled ROW MOVEMENT in the table so that shrink operations can be performed on the table if recommended by Segment Advisor.

After Segment Advisor has been invoked to give recommendations, the findings from Segment Advisor are available in the DBA_ADVISOR_FINDINGS data dictionary view. To show the potential benefits of shrinking segments when Segment Advisor recommends a shrink operation, the view DBA_ADVISOR_RECOMMENDATIONS provides the recommended shrink operation along with the potential savings, in bytes, for the operation.

To set up Segment Advisor to analyze the HR.EMPLOYEES table, we will use an anonymous PL/SQL block, as follows:

The procedure DBMS_ADVISOR.CREATE_TASK specifies the type of advisor; in this case, it is Segment Advisor. The procedure will return a unique task ID and an automatically generated name to the calling program; we will assign our own description to the task.

Within the task, identified by the uniquely generated name returned from the previous procedure, we identify the object to be analyzed with DBMS_ADVISOR.CREATE_OBJECT. Depending on the type of object, the second through the sixth arguments vary. For tables, we only need to specify the schema name and the table name.

Using DBMS_ADVISOR.SET_TASK_PARAMETER, we tell Segment Advisor to give all possible recommendations about the table. If we want to turn off recommendations for this task, we would specify FALSE instead of TRUE for the last parameter.

Finally, we initiate the Segment Advisor task with the DBMS_ADVISOR.EXECUTE_TASK procedure. Once it is done, we display the identifier for the task so we can query the results in the appropriate data dictionary views.

Now that we have a task number from invoking Segment Advisor, we can query DBA_ADVISOR_FINDINGS to see what we can do to improve the space utilization of the HR.EMPLOYEES table:

The results are fairly self-explanatory. We can perform a segment shrink operation on the table to reclaim space from numerous INSERT, DELETE, and UPDATE operations on the HR.EMPLOYEES table. Because the WORK_RECORD column was added to the HR.EMPLOYEES table after the table was already populated, we may have created some chained rows in the table; in addition, since the WORK_RECORD column can be up to 4000 bytes long, updates or deletes of rows with big WORK_RECORD columns may create blocks in the table with free space that can be reclaimed. The view DBA_ADVISOR_RECOMMENDATIONS provides similar information:

In any case, we will shrink the segment HR.EMPLOYEES to reclaim the free space. As an added time-saving benefit to the DBA, the SQL needed to perform the shrink is provided in the view DBA_ADVISOR_ACTIONS:

As mentioned earlier, the shrink operation does not require extra disk space and does not prevent access to the table during the operation, except for a very short period of time at the end of the process to free the unused space. All indexes are maintained on the table during the operation.

In addition to freeing up disk space for other segments, there are other benefits to shrinking a segment. Cache utilization is improved because fewer blocks need to be in the cache to satisfy SELECT or other DML statements against the segment. Also, because the data in the segment is more compact, the performance of full table scans is improved.

There are a couple of caveats and minor restrictions in Oracle Database 12c. First, segment shrink will not work on SecureFile LOB segments, IOT mapping tables, tables with function-based indexes, and ROWID-based materialized views. If a table is compressed, only certain compression types are eligible for shrinking, such as advanced compression using ROW STORE COMPRESS ADVANCED. You could uncompress the table before shrinking it—but then you’d be better off using ALTER TABLE … MOVE ONLINE and specifying the same compression storage parameters for the move.

Undo Advisor and the Automatic Workload Repository

Starting with Oracle 10g, Undo Advisor provides tuning information for the undo tablespace, whether it’s sized too large, it’s too small, or the undo retention (via the initialization parameter UNDO_RETENTION) is not set optimally for the types of transactions that occur in the database.

Using Undo Advisor is similar to using Segment Advisor in that we will call the DBMS_ADVISOR procedures and query the DBA_ADVISOR_* data dictionary views to see the results of the analysis.

Undo Advisor, however, relies on another feature introduced in Oracle 10g—the Automatic Workload Repository (AWR). The Automatic Workload Repository, built into every Oracle database, contains snapshots of all key statistics and workloads in the database at 60-minute intervals by default. The statistics in the AWR are kept for seven days, after which the oldest statistics are dropped. Both the snapshot intervals and the retention period can be adjusted to suit your environment, however. The AWR maintains the historical record of how the database is being used over time and helps to diagnose and predict problems long before they can cause a database outage.

To set up Undo Advisor to analyze undo space usage, we will use an anonymous PL/SQL block similar to what we used for Segment Advisor. Before we can use Segment Advisor, however, we need to determine the timeframe to analyze. The data dictionary view DBA_HIST_SNAPSHOT contains the snapshot numbers and date stamps; we will look for the snapshot numbers from 8:00 P.M. Saturday, July 21, 2013 through 9:30 P.M. Saturday, July 21, 2013:

Given these results, we will use a SNAP_ID range from 6 to 8 when we invoke Undo Advisor. The PL/SQL anonymous block is as follows:

As with Segment Advisor, we can review the DBA_ADVISOR_FINDINGS view to see the problem and the recommendations.

In this particular scenario, Undo Advisor indicates that there is enough space allocated in the undo tablespace to handle the types and volumes of queries run against this database.

Index Usage

Although indexes provide a tremendous benefit by speeding up queries, they can have an impact on space usage in the database. If an index is not being used at all, the space occupied by an index can be better used elsewhere; if we don’t need the index, we also can save processing time for INSERT, UPDATE, and DELETE operations that have an impact on the index. Index usage can be monitored with the dynamic performance view V$OBJECT_USAGE. In our HR schema, we suspect that the index on the JOB_ID column of the EMPLOYEES table is not being used. We turn on monitoring for this index as follows:

We take a quick look at the V$OBJECT_USAGE view to make sure this index is being monitored:

The column USED will tell us if this index is accessed to satisfy a query. After a full day of typical user activity, we check V$OBJECT_USAGE again and then turn off monitoring:

Sure enough, the index appears to be used at least once during a typical day.

On the other end of the spectrum, an index may be accessed too frequently. If key values are inserted, updated, and deleted frequently, an index can become less efficient in terms of space usage. The following commands can be used as a baseline for an index after it is created, and then run periodically to see if the space usage becomes inefficient:

NOTE

Running ANALYZE INDEX … VALIDATE STRUCTURE will temporarily lock the index in exclusive mode and therefore no DML can occur on the table while the ANALYZE is running.

The PCT_USED column indicates the percentage of the allocated space for the index in use. Over time, the EMPLOYEES table is heavily used, due to the high turnover rate of employees at the company, and this index, among others, is not using its space efficiently, as indicated by the following ANALYZE command and SELECT query, so we decide that a rebuild is in order:

Notice the inclusion of the ONLINE option in the ALTER INDEX … REBUILD statement. The indexed table can remain online with minimal overhead while the index is rebuilding. In rare circumstances, such as on longer key lengths, you may not be able to use the ONLINE option.

Space Usage Warning Levels

Earlier in this chapter, we reviewed the data dictionary view DBA_THRESHOLDS, which contains a list of the active metrics to measure a database’s health. In a default installation of Oracle 12c, use the following SELECT statement to see some of the 22 built-in thresholds:

In terms of space usage, we see that the warning level for a given tablespace is when the tablespace is 85 percent full, and the space is at a critical level when it reaches 97 percent full. In addition, this condition need only occur during one reporting period, which by default is one minute. For the other conditions in this list, the condition must be true anywhere between 2 and 15 consecutive reporting periods before an alert is issued.

To change the level at which an alert is generated, we can use the DBMS_SERVER_ALERT.SET_THRESHOLD procedure. In this example, we want to be notified sooner if a tablespace is running out of space, so we will update the warning threshold for alert notification from 85 percent down to 60 percent:

Checking DBA_THRESHOLDS again, we see the warning level has been changed to 60 percent:

A detailed example of how to use Oracle’s Advanced Queuing to subscribe to queue alert messages is beyond the scope of this book. Later in this chapter, I will, however, show some examples of how to use Cloud Control 12c to set up asynchronous notification of alert conditions using e-mail, a pager, or a PL/SQL procedure.

Resumable Space Allocation

The Oracle database provides a way to suspend long-running operations in the event of space allocation failures. Once the DBA is notified and the space allocation problem has been corrected, the long-running operation can complete. The long-running operation does not have to be restarted from the beginning.

Three types of space management problems can be addressed with Resumable Space Allocation:

Out of space in the tablespace

Maximum extents reached in the segment

Space quota exceeded for a user

The DBA can automatically make statements resumable by setting the initialization parameter RESUMABLE_TIMEOUT to a value other than 0. This value is specified in seconds. At the session level, a user can enable resumable operations by using the ALTER SESSION ENABLE RESUMABLE command:

In this case, any long-running operation that may run out of space will suspend for up to 3600 seconds (60 minutes) until the space condition is corrected. If it is not corrected within the time limit, the statement fails.

In the scenario that follows, the HR department is trying to add the employees from the branch office EMPLOYEES table to an EMPLOYEE_SEARCH table that contains employees throughout the company. Without Resumable Space Allocation, the HR user receives an error, as follows:

After running into this problem many times, the HR user decides to use Resumable Space Allocation to prevent a lot of rework whenever there are space problems in the database, and tries the operation again:

The user does not receive a message, and it is not clear that the operation has been suspended. However, in the alert log (managed by the Automatic Diagnostic Repository as of Oracle Database 11g), the XML message reads as follows:

The DBA receives a pager alert, set up in OEM, and checks the data dictionary view DBA_RESUMABLE:

The DBA notices that the tablespace USERS9 does not allow autoextend, and modifies the tablespace to allow growth:

The user session’s INSERT completes successfully, and the status of the resumable operation is reflected in the DBA_RESUMABLE view:

The alert log file also indicates a successful resumption of this operation:

In Figure 6-3, you can see the tablespace USERS9 space alert appear on the instance’s home page in the Incidents and Problems section, in addition to the previous alert warning you that the USERS9 tablespace was nearly full about 15 minutes before the HR user temporarily ran out of space! In Figure 6-4, the Alert History page shows the most recent alerts, which include the tablespace full condition.

FIGURE 6-3. Incidents and Problems section on the instance home page

FIGURE 6-4. Alert History page

As far as the user is concerned, the operation took longer than expected but still completed successfully. Another way to provide more information to the user is to set up a special type of trigger introduced in Oracle9i called a system trigger. A system trigger is like any other trigger, except it is based on some type of system event rather than on a DML statement against a table. Here is a template for a system trigger that fires on an AFTER SUSPEND event:

Managing Alert and Trace Files with ADR

New as of Oracle Database 11g, the Automatic Diagnostic Repository (ADR) is a system-managed repository for storing database alert logs, trace files, and any other diagnostic data previously controlled by several other initialization parameters.

The initialization parameter DIAGNOSTIC_DEST sets the base location for all diagnostic directories; in the examples that follow, the database dw has a value of /u01/app/oracle for the parameter DIAGNOSTIC_DEST. Figure 6-5 shows a typical directory structure starting with the subdirectory /u01/app/oracle/diag.

FIGURE 6-5. ADR directory structure

Notice that there are separate directories for the ASM databases and the database (RDBMS) instances; within the rdbms directory, you can see the dw directory twice: the first-level directory is the database dw, and the second dw is the instance dw. If this were a RAC database, you would see each instance of the dw database under the first-level dw directory. In fact, Oracle strongly recommends that all instances within a RAC database have the same value for DIAGNOSTIC_DEST.

Because the location of all logging and diagnostic information is controlled by the initialization parameter DIAGNOSTIC_DEST, the following initialization parameters are ignored:

BACKGROUND_DUMP_DEST

USER_DUMP_DEST

CORE_DUMP_DEST

For backward compatibility, however, you can still use these as read-only parameters to determine the location of the alert log, trace files, and core dumps:

You can still alter the values for these parameters, but they are ignored by ADR. Alternatively, you can use the view V$DIAG_INFO to find all diagnostic-related directories for the instance:

OS Space Management

Outside of the Oracle environment, space should be monitored by the system administrator with a thorough understanding from the DBA as to the parameters in place for autoextending datafiles. Setting AUTOEXTEND ON with large NEXT values for a tablespace will allow a tablespace to grow and accommodate more inserts and updates, but this will fail if the server’s disk volumes do not have the space available. Better yet is to use ASM: The storage administrator will allocate one or more large blocks of disk space or entire disks from a storage appliance, allowing the Oracle DBA to manage the space entirely from a database perspective.

Space Management Scripts

In this section, I provide a couple scripts you can run on an as-needed basis, or you can schedule them to run on a regular basis to proactively monitor the database.

These scripts take the dictionary views and give a more detailed look at a particular structure. The functionality of some of these scripts might overlap with the results provided by some of the tools I’ve mentioned earlier in the chapter, but they might be more focused and in some cases provide more detail about the possible space problems in the database.

Segments That Cannot Allocate Additional Extents

In the following script, we want to identify segments (most likely tables or indexes) that cannot allocate additional extents:

In this example, we’re using a correlated subquery to compare the size of the next extent to the amount of free space left in the tablespace. The other condition we’re checking is whether the next extent request will fail because the segment is already at the maximum number of extents.

The reason these objects might be having problems is most likely one of two possibilities: The tablespace does not have room for the next extent for this segment, or the segment has the maximum number of extents allocated. To solve this problem, the DBA can extend the tablespace by adding another datafile or by exporting the data in the segment and re-creating it with storage parameters that more closely match its growth pattern. Since Oracle9i, using locally managed tablespaces instead of dictionary-managed tablespaces solves this problem when disk space is not the issue, because the maximum number of extents in an LMT is unlimited.

Used and Free Space by Tablespace and Datafile

The following SQL*Plus script breaks down the space usage of each tablespace, which is further broken down by datafile within each tablespace. This is a good way to see how space is used and extended within each datafile of a tablespace, and it may be useful for load balancing when you’re not using ASM or other high-availability storage.

Only the USERS9 tablespace has more than one datafile in this database. To include temporary tablespaces on this report, you can use a UNION query to combine this query with a similar query based on V$TEMPFILE.

Automating and Streamlining the Notification Process

Although any of the scripts and packages presented earlier in this chapter can be executed on demand, some of them can and should be automated, not only to save time for the DBA but also to be proactive and catch problems long before they cause a system outage.

Two of the primary methods for automating the scripts and packages are DBMS_SCHEDULER and Oracle Cloud Control 12c. Each of these methods has its advantages and disadvantages. DBMS_SCHEDULER can provide more control over how the task is scheduled and can be set up using only a command-line interface. Oracle Cloud Control, on the other hand, uses a completely web-based environment that allows a DBA to oversee a database environment from wherever there is access to a web browser.

Using DBMS_SCHEDULER

The DBMS_SCHEDULER package has been available since Oracle Database 11g. It provides new features and functionality over the previous job scheduler package, DBMS_JOB. Although DBMS_JOB is still available in Oracle Database 12c, it is deprecated and no longer supported.

DBMS_SCHEDULER contains many of the procedures you’d expect from a scheduling package: CREATE_JOB, DROP_JOB, DISABLE, STOP_JOB, and COPY_JOB. In addition, DBMS_SCHEDULER makes it easy to automatically repeat job executions with CREATE_SCHEDULE and to partition jobs into categories based on resource usage with the CREATE_JOB_CLASS procedure.

Cloud Control and Monitoring

Not only can Oracle Enterprise Manager present most database administration tasks in a graphical, web-based environment, it can automate some of the routine tasks that a DBA might perform on a daily basis. In this section, we’ll cover the OEM-equivalent functionality to Segment Advisor and Undo Advisor, covered previously in this chapter.

Segment Advisor

Figure 6-6 shows the home page for the RPT12C database in Cloud Control. Many of the space management functions, including Segment Advisor, are available directly from this home page, especially when there is a pending alert.

FIGURE 6-6. OEM home page

The home page lists general availability information of the instance, including the instance name, host name, CPU usage, and session information. Links to the advisors are in the drop-down lists in the upper-left corner.

If there is not an outstanding space-related alert, and you want to run Segment Advisor, go to the Advisor Central page by choosing Performance | Advisor Central; you will see the page shown in Figure 6-7. Click the Segment Advisor link, and you will see the page shown in Figure 6-8. Select the Schema Objects radio button and click Next.

FIGURE 6-7. Advisor Central page

FIGURE 6-8. Segment Advisor Step 1: Selecting the Schema Objects analysis type

In Figure 6-9, the table HR.TEMP_OBJ has been selected for analysis.

FIGURE 6-9. Segment Advisor Step 2: Selected objects

When you click Next on the page shown in Figure 6-9, you can change the scheduling for the analysis job; by default, the job runs immediately, which is what you want to do in this case. Figure 6-10 shows the scheduling options.

When you click Next in Figure 6-10, you see the review page shown in Figure 6-11. You can click Show SQL if you are curious or if you want to use the SQL statements in your own custom batch job.

FIGURE 6-10. Segment Advisor Step 3: Scheduling options

FIGURE 6-11. Segment Advisor Step 4: Review

As you might suspect, clicking Submit on the page shown in Figure 6-11 submits the job to be run either immediately or at the specified time. The next page you see is the Advisors tab, shown in Figure 6-12. When the job completes you will see the completion status on this page.

FIGURE 6-12. Advisors and Advisor Tasks

Click the Segment Advisor link in the Results section shown in Figure 6-12, and you will see the Recommendations page shown in Figure 6-13.

FIGURE 6-13. Segment Advisor results

The Segment Advisor results in Figure 6-13 indicate that the table TEMP_OBJ in the USERS9 tablespace would benefit from a shrink operation, potentially improving access to the table and freeing up space in the USERS9 tablespace. To implement the recommendation, click the Recommendation Details link shown in Figure 6-13, and on the page shown in Figure 6-14, you can click the Shrink button in the Recommendation column to perform the shrink operation on the TEMP_OBJ table.

FIGURE 6-14. Implementing Segment Advisor recommendations

Undo Advisor

To start Automatic Undo Management Advisor, start at the page shown previously in Figure 6-7, and click the Automatic Undo Management link at the top of the page. In Figure 6-15, you see the current settings for the undo tablespace UNDOTBS1.

FIGURE 6-15. Undo Advisor current settings and options

Given the recent SQL load in this database, the current size of the undo tablespace (265MB) is sufficient (with AUTOEXTEND set at 5MB increments) to satisfy the undo data needs for similar queries in the future. However, you’re expecting to add some data warehouse tables and you may have long-running queries that may exceed the current 15-minute undo retention window, and you want to maintain overall system performance by avoiding frequent extensions to the existing undo tablespace. Therefore, you probably need to increase the size of the undo tablespace; in Figure 6-15, specify 90 minutes in the Duration text box and click the Run Analysis button. The analysis is performed immediately; at the bottom of Figure 6-16, you see that the minimum required undo tablespace size is 143MB.

FIGURE 6-16. Undo Advisor recommendations

You don’t need to change your undo tablespace size immediately; the beauty of Undo Advisor is that you can change the time period for analysis and retention to see what your disk requirements will be in a given scenario.

Summary

It seems you never have enough disk space to hold all of the objects and their data in the database. Even with the ever declining price per GB of storage and the advancements in solid state storage, each GB still has a price attached to it. Given a finite amount of disk space, you must understand how this storage is structured in the database and how to query the structures themselves. You must understand the hierarchy of storage components in the database and this chapter explained that hierarchy with blocks, extents, segments, datafiles, and tablespaces.

Once you know how the storage is structured, you must be able to query that storage metadata. Oracle provides a number of data dictionary and dynamic performance views to show you where and how your storage is allocated. Data dictionary views such as DBA_SEGMENTS shows you the tables, indexes, and materialized views; DBA_EXTENTS shows how those segments were allocated. Dynamic performance views such as V$UNDOSTAT have real-time information about the UNDO tablespace and its occupants.

Oracle not only provides you with these views but gives you an advisor framework to proactively alert you to impending space issues based on thresholds you provide. You can be alerted to space usage at several levels including the tablespace, disk group, and even OS level so that you can maintain an SLA that mandates little or no downtime.