statspack spreport.sql error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: statspack spreport.sql error

  1. #1
    Join Date
    Apr 2007
    Posts
    31

    statspack spreport.sql error

    Statspack was installed in a newer 10g database on Solaris 10. Snapshots were gathered throughout the day for performance tuning information. When I try to execute the report via ?/rdbms/admin/spreport.sql I am prompted for start and end snap id's, then I get this error message:
    Phys Memory (MB):
    ~~~~
    , e.seg_cr_bks_rc_th ecrb
    *
    ERROR at line 48:
    ORA-00904: "E"."SEG_CU_BKS_RC_TH": invalid identifier

    Any clues? I've checked the stats$snapshot table (where the column resides) and everything looks good. I've removed a custom glogin.sql to make sure there were no problems with certain sqlplus parameters for executing the script, still no luck.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by wakedba
    Any clues? I've checked the stats$snapshot table (where the column resides) and everything looks good.
    Lets see... spreport.sql actually executes sprepins.sql which is not fully qualifying the tables. I'm wondering what Oracle account are you using to execute it...

    What happens if you manually execute sprepins.sql query? does it work or does it fails with the same error?

    What happens is you edit sprepins.sql and qualify both instances of stats$snapshot table?

    By the way, you may note some random messages getting into your thread signed by gogu_g, don't pay attention, that's probably a malfunction
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Quote Originally Posted by gopu_g
    You can also check your trace file to find the particular error which is causing the ORA-00904 to occur.
    Haha :-) you are really really funny you did add lot of humor and lightened up the meaning of being a DBA.

    regards
    Hrishy

  4. #4
    Join Date
    Apr 2007
    Posts
    31
    I didn't see any trace output associated with that error.

    I'm executed the spreport.sql as perfstat (owner of the schema).

    I tried executing sprepins.sql as perfstat, same error, modified the file to add perfstat as the table owner and same error.

    This DB was created from RMAN backups, when I execute the sprepins it asks for a dbid and instance number (2 results to choose form both same dbid and same instance number but different names).

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Please do...

    1- Log into the the database -SQL*Plus as perfstat
    2- select user from dual;
    3- desc perfstat.stats$snapshot
    4- select seg_cr_bks_rc_th from perfstat.stats$snapshot where rownum < 2;

    Please post the whole session log.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Apr 2007
    Posts
    31
    PAVB thanks for the info. While going through the motions of your last message I realized what the problem was.

    The DB was a 9i DB that was migrated to 10g, the perfstat was installed and the schema was the 9i version, while I was running the 10g spreport.sql. I was working with another db who is part time (and unavailable most other days) and he set it up yesterday so I assumed it was a new install.

    Anyways, Thanks again.

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Nothing beats going back to the basics when you are troubleshooting.
    I'm glad it worked out.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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