How to Uninstall Oracle Statspack Using the spdrop Script - Complete Guide

calendar Sep 6, 2025 / · 5 min read · oracle database statspack database administration performance tuning sql plus oracle dba database cleanup oracle scripts  ·
Share on: linkedin copy

How to Uninstall Oracle Statspack Using the spdrop Script - Complete Guide

Oracle Statspack uninstallation is accomplished using the @?/rdbms/admin/spdrop command, which completely removes the Statspack performance monitoring framework from your Oracle database. This command removes all associated objects including the PERFSTAT user, tables, procedures, and collected performance data.

Understanding the Query Breakdown

The user query "Uninstall statspack @?/rdbms/admin/spdrop" represents a common Oracle DBA task for removing the Statspack performance monitoring tool. The command structure breaks down into specific components that work together to cleanly remove all Statspack components from the database environment.

What is the spdrop Command

The @?/rdbms/admin/spdrop command is Oracle's official script for completely uninstalling Statspack from an Oracle database. The @? syntax in SQL*Plus refers to the ORACLE_HOME directory, making this command portable across different Oracle installations. This script must be executed by a user with SYSDBA privileges to ensure proper removal of all database objects.

Command Structure Breakdown

The @ Symbol Function

The @ symbol in Oracle SQLPlus is equivalent to the START command and is used to execute SQL scripts. When you use @scriptname, SQLPlus runs the specified script file in the current directory or searches for it in the ORACLE_PATH.

The ? Variable Meaning

The ? symbol represents a predefined SQL*Plus variable that points to the ORACLE_HOME directory. This makes scripts portable across different Oracle installations without hardcoding specific directory paths.

Script Location and Purpose

The spdrop.sql script is located in the rdbms/admin directory under ORACLE_HOME. This directory contains various Oracle database administration scripts for installation, configuration, and maintenance tasks.

Step-by-Step Uninstallation Process

Prerequisites and Requirements

Before running the spdrop script, ensure you have SYSDBA privileges and are connected to the target database. The script requires elevated permissions because it drops database users and removes system-level objects.

Execution Commands

Connect to the database with SYSDBA privileges and execute the following command:

1SQL> CONNECT / AS SYSDBA
2SQL> @?/rdbms/admin/spdrop

What Gets Removed

The spdrop script performs comprehensive cleanup by calling two additional scripts:

  • SPDTAB.SQL: Drops all Statspack tables, synonyms, and packages
  • SPDUSR.SQL: Removes the PERFSTAT user and associated privileges

Objects and Components Affected

PERFSTAT User Removal

The script completely removes the PERFSTAT user, which owns all Statspack objects including tables, indexes, and stored procedures. This user typically resides in a dedicated tablespace called PERFSTAT.

Table and Data Cleanup

All performance statistics tables are dropped, including historical snapshot data, SQL text storage, and system statistics. This includes tables like STATS$SNAPSHOT, STATS$SQLTEXT, and STATS$SYSTEM_EVENT.

Package and Procedure Removal

The STATSPACK package and all associated stored procedures are removed from the database. This eliminates the ability to collect new performance snapshots or generate reports.

Verification and Cleanup

Output File Review

The spdrop script generates two output files that should be reviewed for any errors:

  • SPDTAB.LIS: Contains output from table and object removal
  • SPDUSR.LIS: Contains output from user removal operations

Tablespace Considerations

After running spdrop, you may want to drop the PERFSTAT tablespace if it's no longer needed:

1DROP TABLESPACE PERFSTAT INCLUDING CONTENTS AND DATAFILES;

Verification Queries

Verify complete removal by checking for any remaining Statspack objects:

1SELECT COUNT(*) FROM DBA_OBJECTS WHERE OWNER = 'PERFSTAT';
2SELECT COUNT(*) FROM DBA_USERS WHERE USERNAME = 'PERFSTAT';

Best Practices and Considerations

Backup Before Removal

Always create a backup of your Statspack data before running spdrop if you need to preserve historical performance information. The removal process is irreversible and all collected statistics will be permanently lost.

Alternative Cleanup Methods

For selective cleanup, consider using sppurge.sql to remove specific snapshot ranges instead of complete uninstallation. The sptrunc.sql script removes all snapshot data while preserving the Statspack framework.

Migration to AWR

Modern Oracle databases (10g and later) use Automatic Workload Repository (AWR) instead of Statspack. Consider migrating to AWR for enhanced performance monitoring capabilities before removing Statspack.

Common Issues and Troubleshooting

Permission Errors

If you encounter permission errors, ensure you're connected as a user with SYSDBA privileges. Regular database users cannot drop system-level objects or remove other users.

Incomplete Removal

Review the output files carefully for any error messages. Incomplete removal may leave orphaned objects that require manual cleanup.

Tablespace Dependencies

If the PERFSTAT tablespace contains other objects, you may need to relocate or remove them before dropping the tablespace. Use the INCLUDING CONTENTS clause carefully to avoid data loss.

When to Use Alternative Methods

Partial Data Removal

Use sppurge.sql when you want to remove old snapshots while keeping the Statspack framework active. This is useful for database maintenance without losing the monitoring capability.

Scheduled Cleanup

Implement automated purging using the statspack.purge procedure for ongoing maintenance. This prevents excessive storage usage while maintaining recent performance data.

Reinstallation Preparation

Complete uninstallation with spdrop is recommended when preparing for a clean Statspack reinstallation. This ensures no configuration conflicts or orphaned objects remain.

References

Posts in this series