Automatic refresh of snapshot is failing

From: Anne Nolan <MUNGEanneDOTnolanNOSPAM_at_rts-group.com>
Date: Tue, 08 Jul 2003 09:33:07 -0700
Message-ID: <3F0AF243.57D76239@rts-group.com>

Hello All,

(Oracle 8.1.7.0.0, running on NT4 SP6)

I have a snapshot set up to refresh daily. The setup of the snapshot is:

CREATE MATERIALIZED VIEW DEVEL_222S.MV_QC_BATCH  PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255  STORAGE(
  INITIAL 128 K
  NEXT 128 K
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  PCTINCREASE 0
  FREELISTS 1
  FREELIST GROUPS 1
  BUFFER_POOL DEFAULT
  )
TABLESPACE C14_ACTIVE
LOGGING
NOCACHE
NOPARALLEL
REFRESH COMPLETE
 ON DEMAND
 START WITH TO_DATE('09-Jul-2003 09:42:58','dd-mon-yyyy hh24:mi:ss')  NEXT SYSDATE + 1
 WITH ROWID
 USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE AS
SELECT DISTINCT
a.id field_analysis,
bs.SAMPLE field_sample,
a.METHOD field_method,
a.matrix Field_matrix,
bs.sequence field_sequence,
bs.BATCH,

bs2.sequence qc_sequence,
bs2.SAMPLE qc_sample,
bs2.ANALYSIS qc_analysis,

a2.MATRIX qc_matrix,
a2.METHOD qc_method,

bs2.TYPE QC_TYPE,
bs2.qc_batch_rule,
bs2.exception_rule qc_exception_rule

FROM ANALYSIS a, ANALYSIS a2, BATCH_SLOT bs, BATCH_SLOT bs2, qc_type q WHERE
a.id = bs.ANALYSIS
AND bs.BATCH = bs2.BATCH
AND bs.TYPE IN ('Field')

AND bs2.TYPE NOT IN ('Field')
AND bs2.TYPE = q.name
AND bs2.ANALYSIS = a2.id

AND (q.HAS_ASSOCIATED_SAMPLE = 'N' or
  q.NAME IN ('LCS-DUP'))

When the refresh occurs automatically, the following error appears in the alert log: (the trace file just repeats this info.)

Tue Jul 08 09:42:59 2003
Errors in file f:\oracle817\admin\LIMS\bdump\limsSNP1.TRC:

ORA-12012: error on auto execute of job 141
ORA-01427: single-row subquery returns more than one row
ORA-06512: at line 11

I can manually refresh the snapshot, however, with no errors, by executing:
dbms_refresh.refresh('DEVEL_222S.MV_QC_BATCH');

I don’t see the “subquery” that the error is referring to.

This is my first attempt at using snapshots/materialized views, so if there’s something set up badly here I’d not be surprised.

Any help or suggestions to get the automatic refresh to work would be much appreciated.

Thanks,

Anne Nolan Received on Tue Jul 08 2003 - 11:33:07 CDT