sppurge_auto.sql all of sudden failed

[sfgaldba]

I inherit the script (might be from the core oracle). Basically it runs daily to purge old snapshots, and recompile invlalid objects. I am not familiar why it error off on

F:/Ora_backup/sppurge_070329.log:ORA-06550: line 2, column 28:
F:/Ora_backup/sppurge_070329.log:ERROR at line 2:


I have the script...attached, anyone can help would be great!

I am not sure where it caused the problem?

[davey23uk]

it tells you the line number where the error is, go look at the code.

Do you not have a DBA who can help you with all your questions?

[sfgaldba]

If I have someone here, I would not be asking the question. The problem is not the error, the problem is the script is trying to delete the snapshot from 14 days ago. It is reading stat$snapshot. I do not know why the table is empty now? That is why the error occurred because it is empty. My question is about the how to recreate the stat$snapshot so that it can clean up again? Thanks

[PAVB]

we are facing a mutating question here

As per the last incarnation of the question, stat$snapshot gets populated when a database snapshot is taken.

Do you have a backup?
Do you export statspack schema?
Do you have all statspack stuff in a single, exclusive tablespace?

Can you recover it in another place? Dev or UAT and take a look at what you have there?

[sfgaldba]

What can I do now to get the stat$snapshot re-populated? Do I need to re-create the snapshot?

[PAVB]

let me quote myself...

...stat$snapshot gets populated when a database snapshot is taken.