statspack issue
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: statspack issue

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Posts
    203

    statspack issue

    Guys,

    I executed spcreate.sql and perfstat user get created.

    Then I logged in as perfstat user and tried to execute

    SQL> EXECUTE statspack.snap;

    I am getting the follwing error
    BEGIN statspack.snap; END;

    *
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00201: identifier 'STATSPACK.SNAP' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    can anybody help me!!
    sumit

  2. #2
    Join Date
    Jun 2003
    Location
    India
    Posts
    118
    I think ur statspack user i.e. prefstat, don't have suffecient privilage
    vishal sood
    OCP 8

  3. #3
    Join Date
    Dec 2001
    Posts
    203
    Just to verify , i have granted DBA privileges to perfstat user. Still getting error.
    sumit

  4. #4
    Join Date
    Jun 2003
    Location
    India
    Posts
    118
    Try again after running spcpkg.sql
    vishal sood
    OCP 8

  5. #5
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    182
    You may be trying to execute snapshot.snap procedure other than prefstat user/schema. it should be run only in prefstat schema. when you install statspack it creates prefstat user and grants necessary privileges to this user
    J Gangadhar

  6. #6
    Join Date
    Dec 2001
    Posts
    203
    Vishal,

    I am getting error while running spcpkg.sql

    SQL> @spcpkg.sql
    Creating Package STATSPACK...

    Package created.

    No errors.
    Creating Package Body STATSPACK...

    Warning: Package Body created with compilation errors.

    Errors for PACKAGE BODY STATSPACK:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    291/12 PL/SQL: SQL Statement ignored
    329/19 PL/SQL: ORA-00942: table or view does not exist
    335/14 PL/SQL: SQL Statement ignored
    335/26 PL/SQL: ORA-00942: table or view does not exist
    431/12 PL/SQL: SQL Statement ignored
    431/19 PL/SQL: ORA-00942: table or view does not exist
    494/16 PL/SQL: SQL Statement ignored
    494/28 PL/SQL: ORA-00942: table or view does not exist
    671/13 PL/SQL: SQL Statement ignored
    672/20 PL/SQL: ORA-00942: table or view does not exist
    713/13 PL/SQL: SQL Statement ignored

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    714/20 PL/SQL: ORA-00942: table or view does not exist
    744/13 PL/SQL: SQL Statement ignored
    745/20 PL/SQL: ORA-00942: table or view does not exist
    775/13 PL/SQL: SQL Statement ignored
    776/20 PL/SQL: ORA-00942: table or view does not exist
    814/13 PL/SQL: SQL Statement ignored
    815/20 PL/SQL: ORA-00942: table or view does not exist
    853/13 PL/SQL: SQL Statement ignored
    854/20 PL/SQL: ORA-00942: table or view does not exist

    NOTE:
    SPCPKG complete. Please check spcpkg.lis for any errors.

    SQL>
    sumit

  7. #7
    Join Date
    Jun 2003
    Location
    India
    Posts
    118
    329/19 PL/SQL: ORA-00942: table or view does not exist


    As mentioned in Oracle Documentation for ORA-00942:

    ORA-00942 table or view does not exist

    Cause: The table or view entered does not exist, a synonym that is not allowed here was used, or a view was referenced where a table is required. Existing user tables and views can be listed by querying the data dictionary. Certain privileges may be required to access the table. If an application returned this message, the table the application tried to access does not exist in the database, or the application does not have access to it.

    Action: Check each of the following:

    the spelling of the table or view name.

    that a view is not specified where a table is required.

    that an existing table or view name exists.

    Contact the database administrator if the table needs to be created or if user or application privileges are required to access the table.

    Also, if attempting to access a table or view in another schema, make certain the correct schema is referenced and that access to the object is granted.
    vishal sood
    OCP 8

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    obviously you are missing all these grants, i.e you have not created perfstat properly

    Code:
    pcusr.sql:grant create session              to PERFSTAT;
    spcusr.sql:grant alter  session              to PERFSTAT;
    spcusr.sql:grant create table                to PERFSTAT;
    spcusr.sql:grant create procedure            to PERFSTAT;
    spcusr.sql:grant create sequence             to PERFSTAT;
    spcusr.sql:grant create public synonym       to PERFSTAT;
    spcusr.sql:grant drop   public synonym       to PERFSTAT;
    spcusr.sql:grant select on STATS$X_$KCBFWAIT       to PERFSTAT;
    spcusr.sql:grant select on STATS$X_$KSPPSV         to PERFSTAT;
    spcusr.sql:grant select on STATS$X_$KSPPI          to PERFSTAT;
    spcusr.sql:grant select on STATS$V_$FILESTATXS     to PERFSTAT;
    spcusr.sql:grant select on STATS$V_$TEMPSTATXS     to PERFSTAT;
    spcusr.sql:grant select on STATS$V_$SQLXS          to PERFSTAT;
    spcusr.sql:grant SELECT_CATALOG_ROLE         to PERFSTAT;
    spcusr.sql:grant select on V_$PARAMETER      to PERFSTAT;
    spcusr.sql:grant select on V_$SYSTEM_PARAMETER to PERFSTAT;
    spcusr.sql:grant select on V_$DATABASE       to PERFSTAT;
    spcusr.sql:grant select on V_$INSTANCE       to PERFSTAT;
    spcusr.sql:grant select on V_$LIBRARYCACHE   to PERFSTAT;
    spcusr.sql:grant select on V_$LATCH          to PERFSTAT;
    spcusr.sql:grant select on V_$LATCH_MISSES   to PERFSTAT;
    spcusr.sql:grant select on V_$LATCH_CHILDREN to PERFSTAT;
    spcusr.sql:grant select on V_$LATCH_PARENT   to PERFSTAT;
    spcusr.sql:grant select on V_$ROLLSTAT       to PERFSTAT;
    spcusr.sql:grant select on V_$ROWCACHE       to PERFSTAT;
    spcusr.sql:grant select on V_$SGA            to PERFSTAT;
    spcusr.sql:grant select on V_$BUFFER_POOL    to PERFSTAT;
    spcusr.sql:grant select on V_$SGASTAT        to PERFSTAT;
    spcusr.sql:grant select on V_$SYSTEM_EVENT   to PERFSTAT;
    spcusr.sql:grant select on V_$SESSION        to PERFSTAT;
    spcusr.sql:grant select on V_$SESSION_EVENT  to PERFSTAT;
    spcusr.sql:grant select on V_$SYSSTAT        to PERFSTAT;
    spcusr.sql:grant select on V_$WAITSTAT       to PERFSTAT;
    spcusr.sql:grant select on V_$ENQUEUE_STAT   to PERFSTAT;
    spcusr.sql:grant select on V_$SQLAREA        to PERFSTAT;
    spcusr.sql:grant select on V_$SQL            to PERFSTAT;
    spcusr.sql:grant select on V_$SQLTEXT        to PERFSTAT;
    spcusr.sql:grant select on V_$SESSTAT        to PERFSTAT;
    spcusr.sql:grant select on V_$BUFFER_POOL_STATISTICS to PERFSTAT;
    spcusr.sql:grant select on V_$RESOURCE_LIMIT to PERFSTAT;
    spcusr.sql:grant select on V_$DLM_MISC       to PERFSTAT;
    spcusr.sql:grant select on V_$UNDOSTAT       to PERFSTAT;
    spcusr.sql:grant select on V_$SQL_PLAN       to PERFSTAT;
    spcusr.sql:grant select on V_$DB_CACHE_ADVICE to PERFSTAT;
    spcusr.sql:grant select on V_$PGASTAT        to PERFSTAT;
    spcusr.sql:grant select on V_$INSTANCE_RECOVERY to PERFSTAT;
    spcusr.sql:grant select on V_$SHARED_POOL_ADVICE     to PERFSTAT;
    spcusr.sql:grant select on V_$SQL_WORKAREA_HISTOGRAM to PERFSTAT;
    spcusr.sql:grant select on V_$PGA_TARGET_ADVICE      to PERFSTAT;
    spcusr.sql:grant select on V_$SEGSTAT                  to PERFSTAT;
    spcusr.sql:grant select on V_$SEGMENT_STATISTICS       to PERFSTAT;
    spcusr.sql:grant select on V_$SEGSTAT_NAME             to PERFSTAT;
    spcusr.sql:grant execute on DBMS_SHARED_POOL to PERFSTAT;
    spcusr.sql:grant execute on DBMS_JOB         to PERFSTAT;

  9. #9
    Join Date
    Jun 2000
    Posts
    295
    try to run: spdrop.sql and then spcreate.sql as
    sysdba

  10. #10
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    I've also found that if you want to reinstall statspack you need to comment out whenever sqlerror exit; out of spcusr.sql (called by spcreate.sql) because the drop script doesnt drop the x$ views and synonyms so falls over when you try and re-create them

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