Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Automatic refresh of snapshot is failing

Re: Automatic refresh of snapshot is failing

From: Anne Nolan <MUNGEanneDOTnolanNOSPAM_at_rts-group.com>
Date: Thu, 10 Jul 2003 09:08:22 -0700
Message-ID: <3F0D8F76.3A6C80C4@rts-group.com>


Tom Best wrote:

> 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,

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 Received on Thu Jul 10 2003 - 11:08:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US