Oracle Database Statspack Snapshot Purge Script (sppurge): Complete Guide to sppurge Administration

calendar Sep 2, 2025 / · 4 min read · oracle-database statspack performance monitoring database administration snapshot management storage optimization dba scripts oracle tuning  ·
Share on: linkedin copy

Oracle Database Statspack Snapshot Purge Script: Complete Guide to sppurge Administration

The Oracle Database @?/rdbms/admin/sppurge; command is a critical administrative script used to delete old Statspack snapshots and manage storage space in the PERFSTAT schema. This comprehensive guide explores the purpose, functionality, and best practices for using this essential database performance monitoring tool.

Purpose and Overview

The sppurge.sql script serves as Oracle's official method for purging unnecessary Statspack snapshot data from the PERFSTAT schema. Statspack is Oracle's performance monitoring and diagnosis tool that captures system statistics over time, allowing database administrators to analyze performance trends and identify bottlenecks. However, without proper maintenance, these snapshots can consume significant storage space, making the purge functionality essential for ongoing database administration.

Breakdown of the Code Query

Below is used to delete one or more snapshots

1@?/rdbms/admin/sppurge;

The command structure breaks down as follows:

Script Location and Execution

  • @? - Oracle's substitution variable representing the $ORACLE_HOME directory path
  • /rdbms/admin/ - Standard Oracle directory containing database administration scripts
  • sppurge - The actual purge script filename (with implicit .sql extension)
  • ; - SQL statement terminator

Functional Components

The script operates by connecting to the PERFSTAT user schema and identifying snapshots within a specified range for deletion. When executed, it displays available snapshots and prompts administrators to specify the low and high snapshot IDs for purging.

Key Features and Functionality

Interactive Snapshot Selection

The sppurge script provides an interactive interface that displays the current database instance information and available snapshots before prompting for deletion parameters. This prevents accidental data loss by showing administrators exactly which snapshots will be affected.

Range-Based Deletion

Rather than deleting individual snapshots, the script accepts a range specification using low and high snapshot IDs, making it efficient for bulk cleanup operations. All snapshots falling within the specified range are systematically purged from the PERFSTAT schema.

Baseline Protection

The script includes built-in protection for snapshots marked as baselines, ensuring that critical performance reference points are not accidentally deleted during purge operations. This safety feature maintains important historical data for long-term performance analysis.

Technical Implementation Details

Storage Requirements

Purging snapshots can require substantial rollback segment space because the operation deletes all data related to each snapshot ID in a single transaction. Administrators should either specify smaller snapshot ranges or explicitly use large rollback segments before execution.

Batch Mode Operation

The script supports automated execution through batch mode by pre-defining the losnapid and hisnapid variables, eliminating the need for interactive prompts in scheduled maintenance operations.

Schema Impact

The purge operation affects multiple PERFSTAT schema tables simultaneously, including stats$snapshot, stats$sqltext, stats$undostat, and other related performance data repositories.

Best Practices and Recommendations

Backup Before Purging

Oracle strongly recommends exporting the PERFSTAT schema as a backup before running the purge script, using either custom export parameters or the provided spuexp.par parameter file. This precaution allows data recovery if the purge operation removes more data than intended.

Automated Purge Scheduling

Database administrators can automate snapshot purging using Oracle's job scheduling mechanisms like DBMS_JOB or DBMS_SCHEDULER to maintain consistent storage management. This approach prevents storage space issues from developing over time.

Retention Policy Planning

Establish clear retention policies for Statspack data based on business requirements, typically ranging from 15 to 90 days depending on performance analysis needs and storage constraints. Regular purging prevents the PERFSTAT tablespace from filling up.

Advanced Usage Scenarios

Programmatic Purging

Advanced administrators can create custom purge procedures using the statspack.purge() function with specific day parameters, such as exec statspack.purge(15); to remove all snapshots older than 15 days.

Cross-Instance Management

For complex environments with multiple database instances, administrators can modify the standard sppurge.sql script to handle different DBID values and manage snapshots across various database copies.

Performance Monitoring Integration

The purge script integrates with broader Statspack workflows including snapshot collection automation (spauto.sql) and performance reporting (spreport.sql), creating a comprehensive database monitoring ecosystem.

Troubleshooting Common Issues

Space Reclamation Problems

If purged snapshots don't immediately free up tablespace space, consider adjusting PCTUSED parameters for PERFSTAT tables from the default 40% to 70-80% to improve space reuse. Additionally, identify specific tables that aren't reusing freed space effectively.

Rollback Segment Errors

When encountering rollback segment extension errors during purge operations, either reduce the snapshot range being purged or explicitly specify a larger rollback segment using SET TRANSACTION USE ROLLBACK SEGMENT before script execution.

Version Compatibility

For Oracle 12c and later versions, review the Statspack documentation for any version-specific modifications needed for proper script execution, as the tool was originally designed for earlier Oracle versions.

References

Posts in this series