DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: statspack

  1. #1
    Join Date
    Dec 1999
    Posts
    15
    I have installed the statspack 8.1.7 and hit error when try to gather the statspack snapshot. The error message is as follows:
    ERROR at line 1:
    ORA-04068: existing state of packages has been discarded
    ORA-04063: package body "SYS.DBMS_UTILITY" has errors
    ORA-06508: PL/SQL: could not find program unit being called
    ORA-06512: at "SYS.DBMS_SHARED_POOL", line 45
    ORA-06512: at "SYS.DBMS_SHARED_POOL", line 53
    ORA-06512: at "PERFSTAT.STATSPACK", line 1764
    ORA-06512: at line 1

    I am running oracle 8.1.6 on W2K. Pls help.


  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well it says

    sys.dbms_utility has errors, is this package compiled correctly

  3. #3
    Join Date
    Dec 1999
    Posts
    15

    Angry

    Found that the package body for dbms_utility is invalid and tried re-compile but got error:SQL> alter package

    SQL>dbms_utility compile body;

    Warning: Package Body altered with compilation errors.

    SQL> show errors
    Errors for PACKAGE BODY DBMS_UTILITY:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    35/13 PLS-00323: subprogram or cursor 'COMPILE_SCHEMA' is declared in a
    package specification and must be defined in the package body

    333/10 PLS-00323: subprogram or cursor 'IS_BIT_SET' is declared in a
    package specification and must be defined in the package body


    I have tried re-run dbmsutil.sql but still didn't work. Any ideas what could have gone wrong?


  4. #4
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    Hi,

    I have run the statspack standard creation scripts on several different databases of versions 8.1.6 and 8.1.7.

    In some instances the installation has invalidated some of the Oracle standard packages. Where this has occurred, some of them have actually failed to recompile.

    I raised the issue with Oracle, and apparently this problem is quite notorious.

    The only solution they suggested to me was to rerun catproc and catalog; like you do when you first create a database.

    This sorted out the problems for me.

  5. #5
    Join Date
    Oct 2001
    Posts
    122
    Remeber statspack scripts are supplied with 8.1.7 by default and you should use scripts from ORACLE_HOME/rdbms/admin after install. Separtely downloaded scripts sometime don't work.



  6. #6
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    Hmm,

    I'm sure there was a release of statspack for 8.1.6 too. The creation and report scripts that you use have different naming conventions under each release.

    True - the scripts will be in $ORACLE_HOME/rdbms/admin.

    Under 8.1.6:

    The create script is called statscre.sql
    The drop script is called statsdrp.sql
    The report script is called statsrep.sql

    Under 8.1.7: (Respectively)
    spcreate.sql
    spdrop.sql
    spreport.sql

    T.

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