How to Remove an Oracle AWR Baseline Using DROP_BASELINE

calendar Sep 30, 2025 / · 4 min read · Oracle AWR Database Performance DBA PL/SQL Performance Tuning Baseline Management Workload Repository  ·
Share on: linkedin copy

How to Remove an Oracle AWR Baseline Using DROP_BASELINE

Purpose

The DROP_BASELINE procedure removes a previously-defined AWR (Automatic Workload Repository) baseline from an Oracle database. This command is essential for database administrators who need to conserve storage space by deleting unused or outdated performance baselines while maintaining the flexibility to preserve or purge the underlying snapshot data.

Breakdown of Code

Query:

1exec dbms_workload_repository.drop_baseline('<baseline name>')

The DROP_BASELINE procedure is part of the DBMS_WORKLOAD_REPOSITORY package and removes a specific baseline from the Oracle database system. This simplified syntax uses only the baseline name parameter, which means it will drop the baseline definition but preserve the underlying snapshots by default.

Complete Syntax

The full syntax for the DROP_BASELINE procedure includes three parameters :

1DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(
2  baseline_name IN VARCHAR2,
3  cascade       IN BOOLEAN DEFAULT FALSE,
4  dbid          IN NUMBER DEFAULT NULL
5);

Parameters Explained

baseline_name: This required parameter specifies the exact name of the baseline to be removed from the system. The baseline name must match an existing baseline definition stored in the DBA_HIST_BASELINE view.

cascade: This optional boolean parameter determines whether the underlying snapshots should also be deleted. When set to FALSE (the default), only the baseline definition is removed, and the snapshots are preserved. Setting this parameter to TRUE will delete both the baseline and all associated snapshots.

dbid: This optional parameter specifies the database identifier for which the baseline should be dropped. If not provided, the procedure defaults to the local database identifier (DBID).

Key Points

Storage Management: Periodically dropping unused baselines helps conserve disk space, especially in production environments where AWR data accumulates over time. Baselines are retained indefinitely by default and are excluded from the automatic AWR purging process.

Snapshot Preservation: The default behavior (cascade => FALSE) preserves the underlying snapshots even after the baseline is removed, allowing administrators to create new baselines from the same time period if needed. This approach provides flexibility in performance analysis without losing valuable historical data.

Verification Process: Before dropping a baseline, administrators should query the DBA_HIST_BASELINE view to review existing baselines and confirm the correct baseline name. After executing the drop operation, querying the same view will confirm that the baseline definition has been successfully removed.

Insights and Best Practices

Use CASCADE with Caution: Setting the cascade parameter to TRUE permanently removes both the baseline and its associated snapshots. This action cannot be undone, so administrators should carefully consider whether the snapshot data may be needed for future analysis or comparison.

Baseline Lifecycle Management: Organizations should establish policies for baseline retention and removal as part of their overall AWR management strategy. This includes determining which baselines represent optimal system performance and should be kept for long-term comparison purposes.

Enterprise Manager Alternative: While the command-line approach using DBMS_WORKLOAD_REPOSITORY is effective, Oracle Enterprise Manager provides a graphical interface for baseline management that may be more convenient for routine operations. The Enterprise Manager interface includes built-in prompts for deciding whether to purge underlying data.

RAC Considerations: In Oracle Real Application Clusters (RAC) environments, baselines can be created at the database level to automatically include all instances, simplifying management across the cluster. The dbid parameter ensures the correct database is targeted in multi-database environments.

Baseline Types: Oracle supports multiple baseline types including fixed baselines (specific time periods) and moving window baselines (dynamic time ranges). Understanding the baseline type helps determine the appropriate removal strategy.

Example with All Parameters: For complete control over the drop operation, administrators can specify all parameters explicitly :

1BEGIN
2  DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(
3    baseline_name => 'peak_baseline',
4    cascade       => FALSE,
5    dbid          => 3310949047
6  );
7END;
8/

Monitoring Impact: After removing baselines, monitor the DBA_HIST_SNAPSHOT view to verify that snapshots are either preserved or removed as intended. This validation ensures the drop operation completed successfully and the storage space was reclaimed if cascade was enabled.

References

Posts in this series