During one of the migration , I saw the statspack is invalid state. It happened because Migration strategy uses export/import. Import was done by using system. When you create Statspack (spcreate) it creates some tables and views in the SYS schema. So during import these were not created due to import was done by system.
Please follow the below script to get STATSPACK in valid state. Let me know incase i have missed something or your problem is any different , i will be happy to address that.
Thanks
===================== Script ==================
alter package statspack compile body;
show errors;
-- See what all objects are missing
GRANT ALTER SESSION TO perfstat;
GRANT CREATE PROCEDURE TO perfstat;
GRANT CREATE PUBLIC SYNONYM TO perfstat;
GRANT CREATE SEQUENCE TO perfstat;
GRANT CREATE SESSION TO perfstat;
GRANT CREATE TABLE TO perfstat;
GRANT DROP PUBLIC SYNONYM TO perfstat;
GRANT EXECUTE ON "SYS"."DBMS_JOB" TO perfstat;
GRANT EXECUTE ON "SYS"."DBMS_SHARED_POOL" TO perfstat;
GRANT SELECT ON "SYS"."STATS$V_$FILESTATXS" TO perfstat;
GRANT SELECT ON "SYS"."STATS$V_$SQLXS" TO perfstat;
GRANT SELECT ON "SYS"."STATS$V_$TEMPSTATXS" TO perfstat;
GRANT SELECT ON "SYS"."STATS$X_$KCBFWAIT" TO perfstat;
GRANT SELECT ON "SYS"."STATS$X_$KSPPI" TO perfstat;
GRANT SELECT ON "SYS"."STATS$X_$KSPPSV" TO perfstat;
GRANT SELECT ON "SYS"."V_$BUFFER_POOL" TO perfstat;
GRANT SELECT ON "SYS"."V_$BUFFER_POOL_STATISTICS" TO perfstat;
GRANT SELECT ON "SYS"."V_$DATABASE" TO perfstat;
GRANT SELECT ON "SYS"."V_$DB_CACHE_ADVICE" TO perfstat;
GRANT SELECT ON "SYS"."V_$DLM_MISC" TO perfstat;
GRANT SELECT ON "SYS"."V_$ENQUEUE_STAT" TO perfstat;
GRANT SELECT ON "SYS"."V_$INSTANCE" TO perfstat;
GRANT SELECT ON "SYS"."V_$INSTANCE_RECOVERY" TO perfstat;
GRANT SELECT ON "SYS"."V_$LATCH" TO perfstat;
GRANT SELECT ON "SYS"."V_$LATCH_CHILDREN" TO perfstat;
GRANT SELECT ON "SYS"."V_$LATCH_MISSES" TO perfstat;
GRANT SELECT ON "SYS"."V_$LATCH_PARENT" TO perfstat;
GRANT SELECT ON "SYS"."V_$LIBRARYCACHE" TO perfstat;
GRANT SELECT ON "SYS"."V_$PARAMETER" TO perfstat;
GRANT SELECT ON "SYS"."V_$PGASTAT" TO perfstat;
GRANT SELECT ON "SYS"."V_$PGA_TARGET_ADVICE" TO perfstat;
GRANT SELECT ON "SYS"."V_$RESOURCE_LIMIT" TO perfstat;
GRANT SELECT ON "SYS"."V_$ROLLSTAT" TO perfstat;
GRANT SELECT ON "SYS"."V_$ROWCACHE" TO perfstat;
GRANT SELECT ON "SYS"."V_$SEGMENT_STATISTICS" TO perfstat;
GRANT SELECT ON "SYS"."V_$SEGSTAT" TO perfstat;
GRANT SELECT ON "SYS"."V_$SEGSTAT_NAME" TO perfstat;
GRANT SELECT ON "SYS"."V_$SESSION" TO perfstat;
GRANT SELECT ON "SYS"."V_$SESSION_EVENT" TO perfstat;
GRANT SELECT ON "SYS"."V_$SESSTAT" TO perfstat;
GRANT SELECT ON "SYS"."V_$SGA" TO perfstat;
GRANT SELECT ON "SYS"."V_$SGASTAT" TO perfstat;
GRANT SELECT ON "SYS"."V_$SHARED_POOL_ADVICE" TO perfstat;
GRANT SELECT ON "SYS"."V_$SQL" TO perfstat;
GRANT SELECT ON "SYS"."V_$SQLAREA" TO perfstat;
GRANT SELECT ON "SYS"."V_$SQLTEXT" TO perfstat;
GRANT SELECT ON "SYS"."V_$SQL_PLAN" TO perfstat;
GRANT SELECT ON "SYS"."V_$SQL_WORKAREA_HISTOGRAM" TO perfstat;
GRANT SELECT ON "SYS"."V_$SYSSTAT" TO perfstat;
GRANT SELECT ON "SYS"."V_$SYSTEM_EVENT" TO perfstat;
GRANT SELECT ON "SYS"."V_$SYSTEM_PARAMETER" TO perfstat;
GRANT SELECT ON "SYS"."V_$UNDOSTAT" TO perfstat;
GRANT SELECT ON "SYS"."V_$WAITSTAT" TO perfstat;
GRANT "SELECT_CATALOG_ROLE" TO perfstat;
CREATE OR REPLACE VIEW "SYS"."STATS$V_$SQLXS" ("SQL_TEXT",
"SHARABLE_MEM","SORTS","MODULE","LOADED_VERSIONS","FETCHES",
"EXECUTIONS","LOADS","INVALIDATIONS","PARSE_CALLS",
"DISK_READS","BUFFER_GETS","ROWS_PROCESSED","COMMAND_TYPE",
"ADDRESS","HASH_VALUE","VERSION_COUNT","CPU_TIME",
"ELAPSED_TIME","OUTLINE_SID","OUTLINE_CATEGORY","IS_OBSOLETE",
"CHILD_LATCH") AS
select max(sql_text) sql_text
, sum(sharable_mem) sharable_mem
, sum(sorts) sorts
, min(module) module
, sum(loaded_versions) loaded_versions
, sum(fetches) fetches
, sum(executions) executions
, sum(loads) loads
, sum(invalidations) invalidations
, sum(parse_calls) parse_calls
, sum(disk_reads) disk_reads
, sum(buffer_gets) buffer_gets
, sum(rows_processed) rows_processed
, max(command_type) command_type
, address address
, hash_value hash_value
, count(1) version_count
, sum(cpu_time) cpu_time
, sum(elapsed_time) elapsed_time
, max(outline_sid) outline_sid
, max(outline_category) outline_category
, max(is_obsolete) is_obsolete
, max(child_latch) child_latch
from v$sql
group by hash_value, address
/
grant select on v_$session to perfstat;
grant select on v_$instance to perfstat;
grant select on v_$database to perfstat;
grant select on stats$v$sqlxs to perfstat;
CREATE OR REPLACE VIEW "SYS"."STATS$V_$FILESTATXS" ("TSNAME",
"FILENAME","PHYRDS","PHYWRTS","READTIM","WRITETIM",
"SINGLEBLKRDS","PHYBLKRD","PHYBLKWRT","SINGLEBLKRDTIM",
"WAIT_COUNT","TIME") AS
select ts.name tsname
, df.name filename
, fs.phyrds
, fs.phywrts
, fs.readtim
, fs.writetim
, fs.singleblkrds
, fs.phyblkrd
, fs.phyblkwrt
, fs.singleblkrdtim
, fw.count wait_count
, fw.time time
from x$kcbfwait fw
, v$filestat fs
, v$tablespace ts
, v$datafile df
where ts.ts# = df.ts#
and fs.file# = df.file#
and fw.indx+1 = df.file#
/
grant select on STATS$V_$FILESTATXS to perfstat;
CREATE OR REPLACE VIEW "SYS"."STATS$V_$TEMPSTATXS" ("TSNAME",
"FILENAME","PHYRDS","PHYWRTS","READTIM","WRITETIM",
"SINGLEBLKRDS","PHYBLKRD","PHYBLKWRT","SINGLEBLKRDTIM",
"WAIT_COUNT","TIME") AS
select ts.name tsname
, tf.name filename
, tm.phyrds
, tm.phywrts
, tm.readtim
, tm.writetim
, tm.singleblkrds
, tm.phyblkrd
, tm.phyblkwrt
, tm.singleblkrdtim
, fw.count wait_count
, fw.time time
from x$kcbfwait fw
, v$tempstat tm
, v$tablespace ts
, v$tempfile tf
where ts.ts# = tf.ts#
and tm.file# = tf.file#
and fw.indx+1 = (tf.file# + (select value from v$parameter where name='db_files'))
/
GRANT SELECT ON STATS$V_$TEMPSTATXS TO PERFSTAT;
CREATE OR REPLACE VIEW "SYS"."STATS$X_$KSPPSV" ("ADDR","INDX",
"INST_ID","KSPPSTVL","KSPPSTDF","KSPPSTVF","KSPPSTCMNT") AS
select "ADDR","INDX","INST_ID","KSPPSTVL","KSPPSTDF",
"KSPPSTVF","KSPPSTCMNT"
from X$KSPPSV;
GRANT SELECT ON STATS$X_$KSPPSV TO PERFSTAT;
CREATE OR REPLACE VIEW "SYS"."STATS$X_$KSPPI" ("ADDR","INDX",
"INST_ID","KSPPINM","KSPPITY","KSPPDESC","KSPPIFLG") AS
select "ADDR","INDX","INST_ID","KSPPINM","KSPPITY","KSPPDESC",
"KSPPIFLG"
from X$KSPPI ;
GRANT SELECT ON STATS$X_$KSPPI TO PERFSTAT;
@dbmspool
grant execute on DBMS_SHARED_POOl to perfstat;
alter package statspack compile body;
-- Package body will have no errors.
