Errors during trying to drop snapshots...

Amaterialized view refresh job was broken (failures = 16). I tried to manually run the job (exec dbms_job.run(70)) and it failed. Here is the error in thealert log:
Errors in file C:\OraDBA\eweb\udump\ORA00314.TRC:
ORA-12012: error onauto execute of job 70
ORA-12008: error in snapshot refresh path
ORA-01752:cannot delete from view without exactly one key-preserved table
ORA-06512: at"SYS.DBMS_SNAPSHOT", line 587
ORA-06512: at "SYS.DBMS_SNAPSHOT", line644
ORA-06512: at "SYS.DBMS_IREFRESH", line 561
ORA-06512: at"SYS.DBMS_REFRESH", line 207
ORA-06512: at line 1
Next, I trieddbms_snapshot.refresh('cap_company','c') and got the ORA-1772, etc. errors.
Ithen tried to drop the materialized view and got this error:
ORA-00600: internalerror code, arguments: [17009], [3], [71226860], [76255984], [66067904], [],[], []
The master table is on an 8.1.5 database running on Sun Solaris for Intel5.7.
A materialized view log exists.

These were some of the logs of your error cause and solutions. Since you have encounterd the ORA-600 at this point, I would suggest that you approach the oracle support, the do the stack trace. You can also check the stack trace at bdump and that would tell you when the trace dump was created, and the rest would only can be made sense out of oracle support.


12012, 00000, "error on auto execute of job %s"
// *Cause: Some kind of error was caught while doing an automatic execute
// of a job.
// *Action: Look at the accompanying errors for details on why the execute
// failed.


12008, 00000, "error in snapshot refresh path"
// *Cause: Table SNAP$_ reads rows from the view
// MVIEW$_, which is a view on the master table
// (the master may be at a remote site). Any
// error in this path will cause this error at refresh time.
// For fast refreshes, the table .MLOG$_
// is also referenced.
// *Action: Examine the other messages on the stack to find the problem.
// See if the objects SNAP$_, MVIEW$_,
// .@, .MLOG$_@
// still exist.



01772, 00000, "Must specify a value for LEVEL"
// *Cause: Expecting the value of LEVEL but something else was specified.
// *Action: Correct the error and reissue the command.


01752, 00000, "cannot delete from view without exactly one key-preserved table"
// *Cause: The deleted table either had no key perserved tables,
// had more than one key-preserved table, or the key-preserved
// table was an unmerged view or a table from a read-only view.
// *Action: Redefine the view or delete it from the underlying base tables.

good luck,
Sam

Hi,

I think you may have to use purge_statistics. Remove all the enteries in the statistics view.

Then try for the dbms_jobs


bye and good luck,

gandhi