Monitoring space use

7.11 Monitoring space use

A very important piece of information for the DBA to know is how much space is available in the data warehouse. One technique to avoid running out of space is to create the data files with autoextend and to define an unlimited number of extents. However, this won't help you if the disk actually fills up. Therefore, as part of your routine monitoring of the data ware-house, you should check for free space. This could be done using Storage Manager in OEM, or you may prefer to simply query the Oracle metadata for this information. Figure 7.51 shows an example of the space utilization for the ORCL instance.

Figure 7.51: Space utilization.

Hint: 

Add an OEM event to notify the DBA of approaching disk space limits.

Inside the Oracle database, there are a significant number of system tables that contain very useful information about the database. You can find a detailed explanation of all of these tables and the information they provide in the Oracle documentation.

7.11.1 Automated space management

Several enhancements have simplified space management. Locally managed tablespaces introduced in Oracle 8i eliminated the need for periodically reorganizing tablespaces to reclaim fragmented space. In Oracle 9i tablespaces can be created with SEGMENT SPACE MANAGEMENT AUTO to automate the management of free space inside a database segment such as a table or index. Automatic undo management eliminates the need to manage rollback segments manually. In Oracle 9i, Oracle managed files (OMF) can be used to create and delete files, that are needed for the data files, on-line logs, and control files, eliminating the need to directly manage the files. All you need to do is specify the location of where you would like the files stored.

7.11.2 Resumable space allocation

Long-running operations that update or add new data to the database can fail when they run out of space. Reexecuting the procedure could take a long time, particularly if it was almost complete when it failed. A new feature in Oracle 9i, resumable statements, makes it possible to intervene and correct errors in the middle of an operation. When the problem that caused the failure is fixed, the operation is automatically resumed.

A resumable statement is suspended when one of the following errors occurs:

• Out of space

• Maximum extents reached

• User space quota exceeded

The following operations are resumable:

• SELECT statements that run out of temporary space (for sort areas)

• DML statements—INSERT, UPDATE, and DELETE

• Import/Export

• SQL*Loader

• DDL statements—CREATE TABLE ... AS SELECT, CREATE INDEX, ALTER INDEX ... REBUILD, ALTER TABLE ... MOVE PARTITION, ALTER TABLE ... SPLIT PARTITION, ALTER INDEX ... REBUILD PARTITION, ALTER INDEX ... SPLIT PARTITION, CREATE MATERIALIZED VIEW, and CREATE MATERIALIZED VIEW LOG

Resumable mode must be enabled for a session. Optionally, you can specify a time-out period. If the error condition is not fixed within that time period, it will abort. You can also specify a name, which can help identify the session that has been suspended in the USER_RESUMABLE or DBA_RESUMABLE views. The following statement sets the time-out period for 3,600 seconds, or one hour, and assigns the name "data ware-house load" to resumable statements for the session.

 SQL> ALTER SESSION ENABLE RESUMABLE      TIMEOUT 3600 NAME 'data warehouse load'; 

SQL*Loader and the import and export utilities provide the same options as command-line parameters.

When a resumable statement is suspended, the error is reported in the alert log. The system also internally generates an AFTER SUSPEND system event. Users can register triggers for this event at both the database and schema level. The triggers can be used to notify the DBA when a statement is suspended, so corrective action can be taken.

Information about the status of resumable statements can be seen by looking at the DBA_RESUMABLE or USER_RESUMABLE views, as shown in the following example. The data warehouse load was suspended, because there was inadequate space to extend the PURCHASE_PRODUCT_INDEX.

 SQL> SELECT STATUS, NAME, ERROR_MSG FROM DBA_RESUMABLE STATUS    NAME      ERROR_MSG --------- -----     --------- SUSPENDED Data      ORA-01683: unable to extend index           Warehouse EASYDW.PURCHASE_PRODUCT_INDEX           Load      partition PURCHASES_FEB02 by 2 in                     tablespace INDX 

When a statement is suspended, the session invoking the statement is put into a wait state. A row is inserted into the V$SESSION_WAIT, as seen in the following example.

 SQL> SELECT EVENT, STATE FROM V$SESSION_WAIT; EVENT                                           STATE ----------------------------------------------- -------------- statement suspended, wait error to be cleared   WAITING 

When space is added, the session will automatically resume. If space is not added within the time-out period, an error will occur.