Create perfstat user, with permissions and jobs

=======================

Create perfstat user and jobs

=======================

set_statspack.sh

#!/bin/bash

. /etc/sh/orash/oracle_login.sh igt

sqlplus /nolog << EOF

connect / as sysdba

define perfstat_password=passwd

define default_tablespace=WORKAREA

define temporary_tablespace=TEMPORARY

@?/rdbms/admin/spcreate.sql

GRANT CREATE JOB TO PERFSTAT;

connect perfstat/&&perfstat_password

ALTER SESSION SET NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss';

SET SERVEROUTPUT ON;

SET ECHO ON;

INSERT INTO STATS\$IDLE_EVENT

SELECT name FROM V\$EVENT_NAME WHERE wait_class='Idle'

MINUS

SELECT event FROM STATS\$IDLE_EVENT;

commit;

DECLARE

  v_hourly_job   NUMBER;

  v_purge_job    NUMBER;

BEGIN

 DBMS_OUTPUT.put_line('Create Statspack Job');

 DBMS_JOB.submit

  (job=> v_hourly_job,

   what=>'DECLARE snap number; BEGIN snap := STATSPACK.snap (i_snap_level=>7); END;',

   next_date=>TRUNC(SYSDATE+1/24,'HH'),

   interval=>'TRUNC(SYSDATE+1/24,''HH'')'

  );

  commit;

  

  DBMS_OUTPUT.put_line('Run Job');

  DBMS_JOB.run(v_hourly_job);

  DBMS_OUTPUT.put_line('Create purge statspack data older than 14 days');  

  DBMS_JOB.submit  

  (job=> v_purge_job,

   what=>'STATSPACK.purge(i_purge_before_date=>sysdate-14,i_extended_purge=>true);',

   next_date => TRUNC(SYSDATE+1)+3/24, 

   interval => 'TRUNC(SYSDATE+1)+3/24'

  );

  commit; 

END;

/

EOF

exit

=======================

Note 1 : Drop user perfstat

=======================

DROP USER perfstat CASCADE;

DROP public synonym STATS$SNAPSHOT_ID;

DROP public synonym STATS$DATABASE_INSTANCE;

DROP public synonym STATS$LEVEL_DESCRIPTION;

DROP public synonym STATS$X$KCBFWAIT;

DROP public synonym STATS$X$KSPPSV;

DROP public synonym STATS$X$KSPPI;

DROP public synonym STATS$X$KSXPPING;

DROP public synonym STATS$V$FILESTATXS;

DROP public synonym STATS$V$TEMPSTATXS;

DROP public synonym STATS$V$SQLXS;

DROP public synonym STATS$V$SQLSTATS_SUMMARY;

DROP public synonym STATS$SNAPSHOT_ID;

DROP public synonym STATS$DATABASE_INSTANCE;

DROP public synonym STATS$LEVEL_DESCRIPTION;

DROP public synonym STATS$SNAPSHOT;

DROP public synonym STATS$DB_CACHE_ADVICE;

DROP public synonym STATS$FILESTATXS;

DROP public synonym STATS$TEMPSTATXS;

DROP public synonym STATS$LATCH;

DROP public synonym STATS$LATCH_CHILDREN;

DROP public synonym STATS$LATCH_PARENT;

DROP public synonym STATS$LATCH_MISSES_SUMMARY;

DROP public synonym STATS$LIBRARYCACHE;

DROP public synonym STATS$BUFFER_POOL_STATISTICS;

DROP public synonym STATS$ROLLSTAT;

DROP public synonym STATS$ROWCACHE_SUMMARY;

DROP public synonym STATS$SGA;

DROP public synonym STATS$SGASTAT;

DROP public synonym STATS$SYSSTAT;

DROP public synonym STATS$SESSTAT;

DROP public synonym STATS$SYSTEM_EVENT;

DROP public synonym STATS$SESSION_EVENT;

DROP public synonym STATS$WAITSTAT;

DROP public synonym STATS$ENQUEUE_STATISTICS;

DROP public synonym STATS$SQL_SUMMARY;

DROP public synonym STATS$SQLTEXT;

DROP public synonym STATS$SQL_STATISTICS;

DROP public synonym STATS$RESOURCE_LIMIT;

DROP public synonym STATS$DLM_MISC;

DROP public synonym STATS$CR_BLOCK_SERVER;

DROP public synonym STATS$CURRENT_BLOCK_SERVER;

DROP public synonym STATS$INSTANCE_CACHE_TRANSFER;

DROP public synonym STATS$UNDOSTAT;

DROP public synonym STATS$SQL_PLAN_USAGE;

DROP public synonym STATS$SQL_PLAN;

DROP public synonym STATS$SEG_STAT;

DROP public synonym STATS$SEG_STAT_OBJ;

DROP public synonym STATS$PGASTAT;

DROP public synonym STATS$PARAMETER;

DROP public synonym STATS$INSTANCE_RECOVERY;

DROP public synonym STATS$STATSPACK_PARAMETER;

DROP public synonym STATS$SHARED_POOL_ADVICE;

DROP public synonym STATS$SQL_WORKAREA_HISTOGRAM;

DROP public synonym STATS$PGA_TARGET_ADVICE;

DROP public synonym STATS$JAVA_POOL_ADVICE;

DROP public synonym STATS$THREAD;

DROP public synonym STATS$FILE_HISTOGRAM;

DROP public synonym STATS$EVENT_HISTOGRAM;

DROP public synonym STATS$TIME_MODEL_STATNAME;

DROP public synonym STATS$SYS_TIME_MODEL;

DROP public synonym STATS$SESS_TIME_MODEL;

DROP public synonym STATS$STREAMS_CAPTURE;

DROP public synonym STATS$STREAMS_APPLY_SUM;

DROP public synonym STATS$PROPAGATION_SENDER;

DROP public synonym STATS$PROPAGATION_RECEIVER;

DROP public synonym STATS$BUFFERED_QUEUES;

DROP public synonym STATS$BUFFERED_SUBSCRIBERS;

DROP public synonym STATS$RULE_SET;

DROP public synonym STATS$OSSTATNAME;

DROP public synonym STATS$OSSTAT;

DROP public synonym STATS$PROCESS_ROLLUP;

DROP public synonym STATS$PROCESS_MEMORY_ROLLUP;

DROP public synonym STATS$SGA_TARGET_ADVICE;

DROP public synonym STATS$STREAMS_POOL_ADVICE;

DROP public synonym STATS$MUTEX_SLEEP;

DROP public synonym STATS$DYNAMIC_REMASTER_STATS;

DROP public synonym STATS$TEMP_SQLSTATS;

DROP public synonym STATS$IOSTAT_FUNCTION_NAME;

DROP public synonym STATS$IOSTAT_FUNCTION;

DROP public synonym STATS$IOSTAT_FUNCTION_DETAIL;

DROP public synonym STATS$MEMORY_TARGET_ADVICE;

DROP public synonym STATS$MEMORY_DYNAMIC_COMPS;

DROP public synonym STATS$MEMORY_RESIZE_OPS;

DROP public synonym STATS$INTERCONNECT_PINGS;

DROP public synonym STATS$IDLE_EVENT;

DROP public synonym STATSPACK;

=======================

Note 2 - scripts

=======================

All perfstat objects are created from these 3 scripts, under $ORACLE_HOME

$ORACLE_HOME/rdbms/admin/spcusr.sql

$ORACLE_HOME/rdbms/admin/spctab.sql

$ORACLE_HOME/rdbms/admin/spcpkg.sql