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

Take a look at the v$sql view and look for the sql that is being run
on behalf of the mv refresh (sql_text has the first several chars of
it). Maybe you will see what the offending subselect in there and can
debug from that.

Tom Best

Tom Best wrote:

Tom,

Thanks for the reply. I had a look at the SQL, but there's no subquery.

I have also now created a very simple test-case materialized view, which
just is basically
"Select * from Mytable" .. no where clause, etc. Simple as I can make
it.

It also generates this same error:

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

Is there some Oracle parameter or permission that's causing this to
happen? I can manually refresh these MVs just fine.

I'm going crazy on this....

The script for the MV is:

CREATE MATERIALIZED VIEW MV_TEST
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 TEMP_JUNK
NOLOGGING
CACHE
REFRESH COMPLETE
ON DEMAND
START WITH TO_DATE('10-Jul-2003 09:19:37','dd-mon-yyyy hh24:mi:ss')
NEXT SYSDATE+1/1440
WITH ROWID
USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE AS
SELECT "USER_LOG"."SCHEMANAME" "SCHEMANAME","USER_LOG"."OSUSER"
"OSUSER","USER_LOG"."MACHINE" "MACHINE","USER_LOG"."PROGRAM"
"PROGRAM","USER_LOG"."LOGON_TIME" "LOGON_TIME","USER_LOG"."LOGOFF_TIME"
"LOGOFF_TIME" FROM "USER_LOG" "USER_LOG"

Anne

On Thu, 10 Jul 2003 09:35:09 -0700, Anne Nolan
<MUNGEanneDOTnolanNOSPAM (AT) rts-group (DOT) com> wrote:

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