SP2-0640 : Not Connected in STATSPACK

[harrygulls]

I am trying to install STATSPACK by running spcreate.sql in Oralce 9i.
It disconnect me everytime by the SP2-0640 : Not Connected error. I do not how is this happening.

Harry

[julian]

And how are you installing STASPACK?

[harrygulls]

I am installing it by running spcreate.sql in SQL*PLUS with SYSTEM account.

[jmodic]

Run it connected as SYS or connected as "SYSTEM AS SYSDBA" (you can replace SYSTEM with any user that has been granted SYSDBA privilege). Running as SYSTEM is not sufficient.

[harrygulls]

Thanks It's working now.

[julian]

I have noticed that if installation of STASPACK fails you need to clean up a bit after dropping PERFSTAT:

8i:
drop user PERFSTAT cascade;
drop view X_$KCBFWAIT;
drop public synonym X$KSPPSV;
drop public synonym X$KCBFWAIT;
drop view X_$KSPPSV;
drop view X_$KSPPI;
drop public synonym X$KSPPI;
drop view X_$KSQST;
drop public synonym X$KSQST;
drop view V_$FILESTATXS;
drop public synonym V$FILESTATXS;
drop view V_$TEMPSTATXS;
drop public synonym V$TEMPSTATXS;
drop view V_$SQLXS;

9i:
drop user PERFSTAT cascade;
drop public synonym STATS$V_$FILESTATXS;
drop public synonym STATS$V_$SQLXS;
drop public synonym STATS$V_$TEMPSTATXS;
drop public synonym STATS$X_$KCBFWAIT;
drop public synonym STATS$X_$KSPPI;
drop public synonym STATS$X_$KSPPSV;
drop public synonym STATS$BG_EVENT_SUMMARY;
drop public synonym STATS$BUFFER_POOL_STATISTICS;
drop public synonym STATS$DATABASE_INSTANCE;
drop public synonym STATS$DB_CACHE_ADVICE;
drop public synonym STATS$DLM_MISC;
drop public synonym STATS$ENQUEUE_STAT;
drop public synonym STATS$FILESTATXS;
drop public synonym STATS$IDLE_EVENT;
drop public synonym STATS$INSTANCE_RECOVERY;
drop public synonym STATS$LATCH;
drop public synonym STATS$LATCH_CHILDREN;
drop public synonym STATS$LATCH_MISSES_SUMMARY;
drop public synonym STATS$LATCH_PARENT;
drop public synonym STATS$LEVEL_DESCRIPTION;
drop public synonym STATS$LIBRARYCACHE;
drop public synonym STATS$PARAMETER;
drop public synonym STATS$PGASTAT;
drop public synonym STATS$PGA_TARGET_ADVICE;
drop public synonym STATS$RESOURCE_LIMIT;
drop public synonym STATS$ROLLSTAT;
drop public synonym STATS$ROWCACHE_SUMMARY;
drop public synonym STATS$SEG_STAT;
drop public synonym STATS$SEG_STAT_OBJ;
drop public synonym STATS$SESSION_EVENT;
drop public synonym STATS$SESSTAT;
drop public synonym STATS$SGA;
drop public synonym STATS$SGASTAT;
drop public synonym STATS$SHARED_POOL_ADVICE;
drop public synonym STATS$SNAPSHOT;
drop public synonym STATS$SNAPSHOT_ID;
drop public synonym STATS$SQLTEXT;
drop public synonym STATS$SQL_PLAN;
drop public synonym STATS$SQL_PLAN_USAGE;
drop public synonym STATS$SQL_STATISTICS;
drop public synonym STATS$SQL_SUMMARY;
drop public synonym STATS$SQL_WORKAREA_HISTOGRAM;
drop public synonym STATS$STATSPACK_PARAMETER;
drop public synonym STATS$SYSSTAT;
drop public synonym STATS$SYSTEM_EVENT;
drop public synonym STATS$TEMPSTATXS;
drop public synonym STATS$UNDOSTAT;
drop public synonym STATS$V$FILESTATXS;
drop public synonym STATS$V$SQLXS;
drop public synonym STATS$V$TEMPSTATXS;
drop public synonym STATS$WAITSTAT;
drop public synonym STATS$X$KCBFWAIT;
drop public synonym STATS$X$KSPPI;
drop public synonym STATS$X$KSPPSV;
drop public synonym STATSPACK;

[jmodic]

Originally posted by julian
I have noticed that if installation of STASPACK fails you need to clean up a bit after dropping PERFSTAT:

8i:
...SNIP...

9i:
...SNIP...

Hm, I wonder why Oracle supply scripts spdrop.sql, spdtab.sql and spdusr.sql under ?\rdbms\admin ..... And not only that, they even bother to mention them (along with the instructions about how and when when to use them) in the documentation!
http://download-west.oracle.com/docs...spac.htm#34046

[davey23uk]

isnt that what spdrop is for?

...ooppss too late