Oracle StatsPack Application
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
Purpose StatsPack is the tool that existed before the introduction of ASH and AWR. There two substantive differences between StatsPack and the newer two. StatsPack is free and doesn't do everything the other two do. But ... it is free ... and it does much of what is required though it does not support newer features. Still, it is something you should check out and be comfortable running when your organization has not licensed Diagnostic and Tuning packs.
Primary StatsPack Creation Scripts
Description Script
Create Script: calls spcusr.sql, spectab.sql, and spcpkg.sql rdbms/admin/sbcreate.sql
Create User and Required Privileges rdbms/admin/spcusr.sql
Create Required Tables rdbms/admin/spctab.sql
Creates Statistics Package rdbms/admin/sbcpkg.sql
Validate Installation conn perfstat/perfstat@orabase
Automates Statistics Collection rdbms/admin/spauto.sql
Server Performance AWR RAC Report rdbms/admin/spawrrac.sql
Purges a Range of Snapshots rdbms/admin/sppurge.sql
Report Configuration rdbms/admin/sprepcon.sql
Default StatsPack Report rdbms/admin/spreport.sql (calls sprepins.sql)
Snapshot Instance Difference Report rdbms/admin/sprepins.sql
Standard StatsPack Report rdbms/admin/sprepsql.sql (calls sprsqins.sql)
Resource Usage, SQL Text & Plans Report rdbms/admin/sprsqins.sql
Truncates the StatsPack Tables rdbms/admin/sptrunc.sql
Drops the user, tables, and package rdbms/admin/spdrop.sql
Drops StatsPack Tables rdbms/admin/spdtab.sql
Drops StatsPack User rdbms/admin/spdusr.sql
Standby StatsPack Creation Scripts
Description Script
Driver Create Script rdbms/admin/sbcreate.sql
(calls sbcusr.sql, sbctab.sql, and sbaddins.sql)
Create User and Required Privileges rdbms/admin/sbcusr.sql
Create Required Tables rdbms/admin/sbctab.sql
Add a Standby Database to the Configuration rdbms/admin/sbaddins.sql (calls sbcpkg.sql)
Create Standby Statistics Package rdbms/admin/sbcpkg.sql
List Standby Instances rdbms/admin/sblisins.sql
Report Creation rdbms/admin/sbreport.sql
(calls sbrepcon.sql and sbrepins.sql)
Return Report Configuration rdbms/admin/sbrepcon.sq
Report Creation rdbms/admin/sbrepins.sql
Purge a Set of Snapshots rdbms/admin/sbpurge.sql
Remove Standby Instance rdbms/admin/sbdelins.sql
Drops the User and Tables rdbms/admin/sbdrop.sql (calls sbdtab.sql and sbdusr.sql)
Drops the Tables rdbms/admin/sbdtab.sql
Drops the User rdbms/admin/sbdrop.sql
 
PDB Deployment
Connect to a PDB and create a StatsPack tablespace and schema [oracle@test21 dbhome_1]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Wed Sep 1 17:53:02 2021
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle. All rights reserved.

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

ALTER SESSION SET CONTAINER=TEST21P1;

Session altered.

sho con_id

CON_ID
------------------------------
3


-- create tablespace
CREATE TABLESPACE perfstat
DATAFILE '+DATA/TEST21DB_IAD25G/B6355315C77F2BECE0531000000A714D/DATAFILE/perfstat01.dbf' SIZE 100M AUTOEXTEND ON MAXSIZE 500M
LOGGING ONLINE PERMANENT BLOCKSIZE 8K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO;

-- create user
SQL> set define on
SQL> @?/rdbms/admin/spcreate.sql

Session altered.

Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: "<complex_pwd_here>"
<complex_pwd_echo'd here>


Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data. Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace. This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS              STATSPACK DEFAULT TABLESPACE
------------------------------ --------------------- ----------------------------
PERFSTAT                       PERMANENT
SYSAUX                         PERMANENT             *
TIER1_TS                       PERMANENT
TIER2_TS                       PERMANENT
TIER3_TS                       PERMANENT
USERS                          PERMANENT
UWDATA                         PERMANENT

Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: PERFSTAT

Using tablespace PERFSTAT as PERFSTAT default tablespace.

Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas). Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS              DB DEFAULT TEMP TABLESPACE
------------------------------ --------------------- --------------------------
CDBROOT_TEMP                   TEMPORARY
TEMP                           TEMPORARY             *

Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

Using tablespace TEMP as PERFSTAT temporary tablespace.

... Creating PERFSTAT user

... Installing required packages

... Creating views

... Granting privileges

NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.

SQL> -- Next two scripts run as perfstat user
SQL> ALTER SESSION SET CURRENT_SCHEMA = PERFSTAT;

Session altered.

SQL> -- Create statspack tables
SQL> @@spctab
...
NOTE:
SPCTAB complete. Please check spctab.lis for any errors.

SQL>
SQL> -- Create the statistics Package
SQL> @@spcpkg
...
SQL> set echo off;
Creating Package STATSPACK...

Package created.

No errors.
Creating Package Body STATSPACK...

Package body created.

No errors.

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

SQL>
SQL> -- Bug#25233027: xxx Set this parameter to FALSE for creating common objects in consolidated database
SQL> alter session set "_oracle_script" = FALSE;

Session altered.

SELECT object_type, COUNT(*)
FROM dba_objects
WHERE owner = 'PERFSTAT'
GROUP BY object_type
ORDER BY 1;

OBJECT_TYPE     COUNT(*)
-------------- ---------
INDEX                 73
PACKAGE                1
PACKAGE BODY           1
SEQUENCE               1
TABLE                 73
VIEW                   1

Related Topics
ASH Report
Automated Workload Repository
AWR Report
DBMS_WORKLOAD_REPOSITORY
STATSPACK package
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx