ORA-03113 while running analyze

[kjnorman]

I am running into a sporadic problem which appears to be getting more common.

Firstly I am running Oracle 8.0.5.1.1 on NT6SP6. I am running my query directly on the server, so a network problem should not be the issue.

The problem I am having is this. I am occasionally getting an ORA-03113: end-of-file on communication channel error when I run the dbms_utility.analyze_schema command (which I like to run every night as part of a job). I regularly get this error on tables that are in the order of >500Mb in size.

I check the all_tables file in the data dictionary to discovery which table causes the problem. If I run

analyze table tablename validate structure cascade;

i get no errors. But if I run

analyze table tablename estimate statistics sample 20 percent;

I get the same -03113 error.

I have found that if I dump the data, drop the table, rebuild it and reload the data, then the problem is solved, until a week or two later then the 03113 error crops up again, either on the same table or another >500Mb table.

Any ideas?

Kerry

 

[sem]

Do you use shared or dedicated connection? Do you connect via IPC or TCP/IP? I suppose that if you connect via sql*net, network errors may occur even for local connections.
As a workaround (if you need it to work only, not to find a reason of this problem) I may sugest to schedule this task via dbms_job to exclude any influence of connection at all.

Regards, Dima