Re: exec STATSPACK.SNAP renders ORA-03113

"Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:q01jqv44qcnqjlg9eu1v8gb1ad09ot5eer_at_4ax.com...
> Hello
>
> I have run the following for a basic Statspack setup:
> @?/rdbms/admin/spcreate
> @?/rdbms/admin/spauto
>
> Everything went well, but after two or three days, no more Snapshots
> were made or were made sporadically. I discovered a hanging perfstat
> session and killed it. After sometime Oracle began to produce core
> dumps every couple of minutes! The alert log was saying something
> about "address not mapped to object". I discovered that all this
> trouble was caused by Statspack. So I removed the hourly job, used
> @?/rdbms/admin/spdrop
> to remove the whole perfstat schema, and the errors disappeared. Then
> I rebuilt the perfstat schema again. Same problems with crashes,
> although installation itself was correct.
>
> If I try
> exec statspack.snap;
> I get an error:
> ORA-03113 End-of-file on communication channel
> and at times, the sqlplus itself crashes (the window disappears).
> I already restarted the instance, problem remains.
>
> This is all running Oracle 8.1.7.0. There is another instance on the
> same server (Sun E3500, Solaris 2.7) using the same version of Oracle
> where Statspack works like a charm.
>
> Default TS of perfstat is TOOLS, temp TS is TEMP. There is enough
> space in TOOLS.

But is there enough space in the statspack tables?? Do they have maxextents set, for example? And does the perfstat user have a quota on TOOLS?

I honestly haven't encountered this problem, so I don't know precisely what the issue, but space (ie, MAXEXTENTS or quota issues) was my first thought. My second thought was that the 8.1.7.3 and .4 patchsets have been out a while now, so why not patch your database upwards with those? Eliminates one possible source of the error.

Regards
HJR
> Any idea?
>
> Rick Denoire
Received on Wed Nov 05 2003 - 17:51:38 CST