Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Automatic refresh of snapshot is failing
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