Free extents

Job details

Parameters

Job Summary

Purpose

• The monitoring job is designed to check free extents in all tablespaces for Oracle databases.
• It ensures that the segments’ storage “next extent” values do not exceed the largest free extent chunk available in any tablespace.

Importance

• This job is critical for maintaining the health, performance, and efficiency of database storage.
• Timely warnings or alarms help prevent potential space allocation issues, which could lead to database performance degradation or halting of transactions.

Manual Checking

• You can manually check the free extents in tablespaces by using the following SQL commands:

select ts_name "Tablespace name", max_free_extent/1024 "Max free extent", max_next_extent/1024 "Max next extent", auto_ext_files "Auto extensible files", tot_files "Total files", extra_comment "Comments", histr_date "Date" from dbw_free_extents_stat order by status asc;

Report Generation

• The job generates a report that includes detailed tables and statistics about tablespaces’ free extents, segment storage status, and alarms or warnings raised:
  • Free extents per tablespace showing maximum available free extent space.
  • Detailed listings for segments exceeding free extents with segment definition warnings.
  • Summarizes issues and provides visibility over potential extend allocation failures or the need for file growth.

Dependencies and SQL Tables

• The job relies on several Oracle views such as dba_free_space, dba_segments, dba_tablespaces, dba_data_files. It includes prepared SQL tables to cache the data:
  • dbw_free_extents_stat tracks tablespaces statistics including the free extent sizes.
  • dbw_segm_storage_def logs segment-level details which could potentially exceed the free extent size.

Implementation Details

• The procedural script free_extents() checks the ‘next extent’ requirements against the ‘max free extents’ for segments in all tablespaces.
• It considers both scenarios where tablespaces are auto-extensible and those which are not. Transactions can fail if the necessary space for a next extent is not available and auto-extension is not possible.

Report Visualization

• Reports feature multiple tables visualizing the status of free vs. required extents, auto-extensible file states, and a summary of findings segmented by tablespaces and their storage configurations.
• The report aims to quickly identify and address tablespaces that may require intervention to prevent storage allocation failures.

Installation and Permissions

• Prior to installation, certain permissions are checked, such as ‘SELECT’ on the necessary Oracle database views. Grant statements are prepared to ensure necessary permissions are set for dbWatch schema to access and operate upon the database views required for monitoring tablespaces.

Benefits

• By automating the monitoring of extent allocation and usage, the job helps database administrators prevent issues related to space shortages and ensures optimal database performance and reliability.
• Automatic alerts and reports provided by this job facilitate proactive database space management, reducing the risk of critical failures due to space allocation issues.