statspack spreport.sql error

[wakedba]

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.

[PAVB]

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

[hrishy]

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

[wakedba]

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).

[PAVB]

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.

[wakedba]

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.

[PAVB]

Nothing beats going back to the basics when you are troubleshooting.
I'm glad it worked out.