Re: All jobs are failing with ORA-01427 (WAS: Automatic refresh of snapshot is failing)

Sybrand Bakker wrote:

> On Thu, 10 Jul 2003 09:35:09 -0700, Anne Nolan
> <MUNGEanneDOTnolanNOSPAM_at_rts-group.com> wrote:
>
> >On further digging (into a different database on a different server we also
> >have), I've discovered that all jobs are failing with the same error:
> >ORA-12012: error on auto execute of job 21
> >ORA-01427: single-row subquery returns more than one row
> >ORA-06512: at line 11
> >
> >Some of these jobs are for materialized view refreshes, while others just run
> >a stored procedure.
> >
> >Both servers run Oracle 8.1.7.0.0
> >One is WinNT4 SP6, while the other is Windows 2000 Version 5.0.
> >
> >Can anyone shed any light as to what's going on?
> >
> >Thanks for any help,
> >
> >Anne Nolan
> >
> >
> >
> >
>
> Isolate the query and execute it independently.
> Alternatively set event='1427 errorstack trace name level 3' in your
> init.ora and bounce the database
>
> >
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

Sybrand,

Thanks for the reply. I'll give that a try when it's a good time to bounce the database. As you will see, there is no query to isolate in some cases.

I created a litte procedure that does nothing but write a line of output via UTL_FILE, stating what time it ran. I set it up to run in the jobs queue once a minute.

The procedure DOES run, even while generating that same error message in the alert log. When I look more closely at my Materialized View refresh job, it also is executing and doing the refresh, but generating the error in the log.

However, the error being generated causes Oracle to set the job as "BROKEN" after 16 runs of the job, and so then it quits running it.

It sounds like either some bug in the jobs stuff, or some Oracle parameter I don't have set right. I'm completely at a loss as to where this single-row subquery is being called.. it's got to be something in Oracle internals. It's certainly not a query I know how to get to.

Thanks again,

Anne Nolan Received on Thu Jul 10 2003 - 14:50:38 CDT