Efficient Space Management

Concepts of and sample questions on space usage

Oracle Database 10g provides a variety of features for managing the space usage of a tablespace. These features include the monitoring of tablespace usage, the ability to suspend transactions that require more space and successfully resume them when the space is available, and a database advisor that lets you determine whether an object has space available for reclamation.

This column presents sample questions on managing tablespace usage. The questions are of the type you may encounter when taking the Oracle Database 10g Administration Workshop II exam. The Oracle Database 10g Administration Workshop II exam is one of the two exams you must take to achieve the Oracle Certified Professional (OCP) level of certification. Note that the sample question format has been adjusted for presentation in this column.

Monitoring Tablespace Space Usage

Oracle Database 10g provides proactive help for managing the disk space usage of a tablespace. It alerts you when tablespaces run low on available space. Two space-used alert thresholds are defined by default: warning and critical. The warning threshold is the limit at which space starts to run low. The critical threshold is a serious limit that warrants your immediate attention. The database issues alerts at both thresholds.

You can modify the default threshold values and also disable the space-used threshold alerts for a tablespace.

You do not want to use the default threshold values for space used for the EXAMPLE tablespace. Which package would you use to explicitly set the space-used threshold values for a tablespace?

A. DBMS_SERVICE
B. DBMS_SERVER_ALERT
C. DBMS_STORAGE_MAP
D. DBMS_TRACE

The correct answer is B. You can use the SET_THRESHOLD procedure in the DBMS_SERVER_ALERT package to set the space-used threshold values for a particular tablespace. Alternatively, you can use Oracle Enterprise Manager 10g to set the space-used threshold values.

The USERS_TBS tablespace does not include autoextensible datafiles. Identify the situation in which server-managed threshold alerts for tablespace space usage will not be generated for the USERS_TBS tablespace.

A. If USERS_TBS is a bigfile tablespace
B. If USERS_TBS is the default permanent tablespace
C. If USERS_TBS is a temporary tablespace
D. If USERS_TBS is a dictionary-managed tablespace
E. If USERS_TBS is an UNDO tablespace

The correct answer is D. Server-managed threshold alerts for tablespace space usage are supported only for locally managed tablespaces, not for dictionary-managed tablespaces. Answers A, B, and C are incorrect because server-managed threshold alerts are supported for bigfile tablespaces, default permanent tablespaces, and temporary tablespaces. Answer E is incorrect because server-managed threshold alerts are supported for UNDO tablespaces.

You created a locally managed tablespace by using the following SQL statement:

CREATE TABLESPACE TEST_TBS

DATAFILE 'test.dat' SIZE 1M 

AUTOEXTEND ON MAXSIZE 100M;

You set the warning threshold limit to 60 percent for the TEST_TBS tablespace. Which statement is correct regarding the server-managed tablespace space usage threshold alerts in this scenario?

A. A warning alert will be generated when 60K of space is used.
B. No alerts will be generated, because it is not a bigfile tablespace.
C. No alerts will be generated, because the tablespace includes an autoextensible datafile.
D. A warning alert will be generated when 60MB of space is used.
E. A warning alert will be generated when 60MB of space is left as free space.

The correct answer is D. Answer A is incorrect because for tablespaces with autoextensible files, the thresholds are computed according to the maximum file size you specified or the maximum file size of the operating system. Answers B and C are incorrect because the alerts are generated for smallfile tablespaces as well as for tablespaces with autoextensible datafiles. Answer E is incorrect because the alerts are based on the space used and not on the free space left in a tablespace.

Resumable Space Allocation

In the event of space allocation failures, rather than returning the error to the user and stopping the operation, the transaction can be temporarily suspended and corrective action taken. After the error condition is corrected, the suspended operation automatically resumes. This feature is called resumable space allocation. The affected statements are called resumable statements.

Which two statements are correct regarding the resumable space allocation feature?

A. Resumable space allocation can be enabled only at the system level and not at the session level.
B. When a resumable statement is suspended, an error is reported in the alert log.
C. Every resumable operation has a "time out" period associated with it.
D. A resumable statement can be suspended and resumed only once during execution.

The correct answers are B and C. When the execution of a resumable statement is suspended, the system issues a Resumable Session Suspended alert. A suspended operation is automatically aborted if the error condition is not fixed within the time-out period. By default, the time-out period is two hours. Answer A is incorrect because you can enable resumable space allocation either at the system level, by setting the RESUMABLE_TIMEOUT initialization parameter to a nonzero value, or at the session level, by issuing the ALTER SESSION ENABLE RESUMABLE statement. Answer D is incorrect because a resumable statement can be suspended and resumed multiple times during execution.

Which statements are resumable? (Choose all that apply.)

A. INSERT INTO ... SELECT from external tables statement
B. CREATE TABLE ... AS SELECT statement
C. CREATE TABLESPACE statement
D. SELECT statement
E. CREATE USER statement

The correct answers are A, B, and D. DML statements, including INSERT INTO ... SELECT from external tables, are resumable. The interface used to execute the DML statements does not matter. DDL statements, including CREATE TABLE ... AS SELECT, are resumable. SELECT statements that run out of temporary space are also candidates for resumable execution.

Under which three error conditions can the resumable space allocation feature be used?

A. The user has exceeded his or her assigned space quota in the tablespace.
B. The number of extents in a table or index equals the number of maximum extents defined on the object.
C. The operation cannot acquire any more extents for an index in a tablespace.
D. The user application tries to access a table for which the user does not have necessary privileges.
E. The user-specified datafile size exceeded the maximum operating system file size.

The correct answers are A, B, and C. A resumable statement can be suspended under any of these conditions: space quota exceeded, maximum extents reached, and out of space.

Database Advisors

Oracle Database 10g provides a set of component advisors that provide useful feedback about resource utilization and performance. These advisors enable you to analyze the performance of your database, identify potential problems and bottlenecks, and tune the various components of your database.

DML operations on the objects within the USER_TBS tablespace have created pockets of empty space that individually are not big enough to be reused. This is leading to a large amount of wasted space in the USER_TBS tablespace. You plan to reclaim the wasted space through a shrink operation. Which advisor would you use to determine the objects in the USER_TBS tablespace that contain wasted space and are good candidates for the shrink operation?

A. SQL Tuning Advisor
B. Segment Advisor
C. Undo Advisor
D. SQL Access Advisor

The correct answer is B. You can use the Segment Advisor to determine whether an object has space available for reclamation on the basis of the level of space fragmentation within the object. The Segment Advisor can generate advice at three levels: object level (such as table), tablespace level, and segment level.

You can use the SQL Tuning Advisor for tuning SQL statements. The Undo Advisor improves transaction management, especially for automatic-undo management. The SQL Access Advisor provides recommendations for creating indexes and materialized views for a given workload.

Conclusion

You can use space-used threshold values to proactively manage the space in tablespaces. Space usage exceeding the threshold values raises alerts, allowing you to take action to ensure that there is space available. Resumable space allocation lets you suspend a statement temporarily, so that when more space is available, the transaction can resume and complete. The Segment Advisor—one of many database advisors—lets you identify unused space so that you can reclaim it.

REFERENCES

Efficient Space Management By Aradhana Puri