SP2-0640 : Not Connected in STATSPACK
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: SP2-0640 : Not Connected in STATSPACK

  1. #1
    Join Date
    Apr 2002
    Posts
    86

    Question SP2-0640 : Not Connected in STATSPACK

    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

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: SP2-0640 : Not Connected in STATSPACK

    And how are you installing STASPACK?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Apr 2002
    Posts
    86
    I am installing it by running spcreate.sql in SQL*PLUS with SYSTEM account.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Apr 2002
    Posts
    86
    Thanks It's working now.

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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;
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    isnt that what spdrop is for?

    ...ooppss too late

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width