statpack installation - liamlamth/blog GitHub Wiki

reference: https://www.dbi-services.com/blog/statspack-with-oracle-database-19c-and-multitenant/

build in cdb and pdb level

sqlplus / as sysdba

SQL>  create tablespace STATPACK datafile '/oracle/psft/SEdbhome/oradata/CDBHRSE/statpack.dbf' size 200M autoextend on maxsize 4G;
SQL>  @?/rdbms/admin/spcreate
Enter value for perfstat_password: 
Enter value for default_tablespace: STATPACK 
Enter value for temporary_tablespace: TEMP
SQL> grant create job to perfstat;

sqlplus perfstat/perfstatpwd

SQL> exec STATSPACK.MODIFY_STATSPACK_PARAMETER (i_snap_level=>7)
SQL> @?/rdbms/admin/spauto.sql
SQL> create or replace procedure extended_purge(
         num_days IN number
     )
     is
     BEGIN
         statspack.purge(i_num_days => num_days, i_extended_purge => TRUE);
     END extended_purge;
     /
SQL> exec dbms_scheduler.create_program(program_name => 'SP_PURGE_PROG', program_type => 'STORED_PROCEDURE', program_action => 
'PERFSTAT.extended_purge', number_of_arguments => 1, enabled => FALSE);
SQL> exec DBMS_SCHEDULER.define_program_argument (program_name => 'SP_PURGE_PROG', argument_name => 'i_num_days', argument_position => 1, argument_type 
=> 'NUMBER', default_value => 30);
SQL> exec dbms_scheduler.enable(name => 'SP_PURGE_PROG');
SQL> exec dbms_scheduler.create_schedule (schedule_name => 'SP_PURGE_SCHED', repeat_interval =>  'freq=weekly; byday=SUN; byhour=0; byminute=20',end_date => null, comments => 'Schedule for Statspack purge');
SQL> exec dbms_scheduler.create_job (job_name => 'SP_PURGE_JOB', program_name => 'SP_PURGE_PROG', schedule_name => 'SP_PURGE_SCHED',  enabled => TRUE, auto_drop => FALSE, comments => 'Statspack Job for purge');

checking

col owner format a15
SQL> select owner, job_name from dba_scheduler_jobs;
col SCHEMA_USER format a15
col INTERVAL format a15
col WHAT format a15
SQL> select name,snap_id,to_char(snap_time,'DD-MON-YYYY:HH24:MI:SS') "Date/Time" from stats$snapshot,v$database;