Running catalog.sql and catproc.sql a second time

[Mark Patterson]

Hi,

I have a database that seems to occasionally display unwarranted error
messages.
For instance - our application will receive an "ORA-00942 table or view
does not exist" error message when the table in question does in fact
exist and the user has the correct privileges.

This only occurs very infrequently, and most often the same statement
works fine. It probably only fails on about one in a hundred executions.

After exhaustively examining the database (fruitlessly!) I'm beginning
to think there may be a corruption or error in the system views and/or
tables. I would like to recreate these if possible.

Is it possible to run catalog.sql and catproc.sql a second time without
damaging the existing schema and stored procedures or can theses scripts
only be run at database creation?

Thanks,
Mark.

[Gollum]

There is no harm done by running catalog and catproc again, but I really
doubt that this will solve your problem.
You are probably not having a corruption (this would be signalled by other
errors than ORA-942), so in order to
locate the problem I suggest you consider setting the following event in
init.ora:

event="942 trace name errorstack forever, level 1"

This will create a tracefile in user_dump_dest each time an ORA-942 is
raised. In this trace you will see the current SQL statement and determine
if any wrong SQL is in fact issued.

HTH,
Gollum

"Mark Patterson" <mpatt...@iel.ie> wrote in message
news:3A9F9088...@iel.ie...

[R. Schierbeek]

Mark,

You can run catalog.sql and catproc.sql a second time -
no problem. Do it as user SYS or INTERNAL though.

Cheers, Roelof

R. Schierbeek, DBA
email : in...@bytelife.com
www : www.bytelife.com
SQL : www.bytelife.com/sql4dba.htm

Mark Patterson <mpatt...@iel.ie> schreef

> Hi,
>
> I have a database that seems to occasionally display unwarranted error