OraFAQ Forum: SQL & PL/SQL » ORA-23404: refresh group does not exist

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-23404: refresh group does not exist

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-23404: refresh group does not exist [message #515348]

Mon, 11 July 2011 00:32

kapilsingla
Messages: 14
Registered: June 2011
Location: Gurgaon

Junior Member

Hi All,

I have got anyother problem here..

When I create a Materialized view using:

CREATE MATERIALIZED VIEW LOAD.M_VW_ABN_PROD_921
[color=red][i]REFRESH FORCE ON DEMAND START WITH sysdate+0 
NEXT (TRUNC(SYSDATE+1) + 9.5/24)[/i][/color]AS 
SELECT
...............
......................

it creates a DBMS_JOB for me with the required interval.

But now my requirement is that I want to create a job on
the existing MV.

Now I tried the code mentioned in the previous post to
schedule the job.

This job does not work. When I tried to execute it manually using:

DBMS_IJOB.RUN(12345) as sys user is throws the follwing error.

Error listed in the alert.log file is as below:

ORA-12012: error on auto execute of job 17497
ORA-23404: refresh group "CLARITY"."M_MV_INBASKET_ANALYSIS" 
does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_REFRESH", line 23
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Sun Jul 10 20:07:51 2011

Report message to a moderator

Re: PLS-00103: Encountered the symbol "NUMBER [message #515349 is a reply to message #515348]

Mon, 11 July 2011 01:08

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Do NOT add a non-related question to a previous topic, create a new one.
I split this message to a new topic "ORA-23404: refresh group does not exist"

Regards
Michel

Report message to a moderator

Re: ORA-23404: refresh group does not exist [message #515350 is a reply to message #515348]

Mon, 11 July 2011 01:10

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Do not just explain us what you did and got, SHOW us: Use SQL*Plus and copy and paste your session, the WHOLE session.
Take care to keep your lines in 80 character width.

Regards
Michel

Report message to a moderator

Re: ORA-23404: refresh group does not exist [message #515353 is a reply to message #515350]

Mon, 11 July 2011 01:23

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I guess that the problem is that you misunderstood how it works. DBMS_REFRESH works on groups of materialized views, not on a materialized view itself. It means that you need to create a group (DBMS_REFRESH.MAKE), put the materialized view into that group (DBMS_REFRESH.ADD), and then refresh the group (DBMS_REFRESH.REFRESH).

Here's the documentation.

Report message to a moderator

Re: PLS-00103: Encountered the symbol "NUMBER [message #515359 is a reply to message #515349]

Mon, 11 July 2011 02:02

kapilsingla
Messages: 14
Registered: June 2011
Location: Gurgaon

Junior Member

Ok, Please ignore this issue now, it is now resolved.
There was a typo in MV name that I was trying
to schedule.

Report message to a moderator

Re: PLS-00103: Encountered the symbol "NUMBER [message #515360 is a reply to message #515359]

Mon, 11 July 2011 02:04

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Something we would see immediatly if you have posted what you did.

Regards
Michel

Report message to a moderator

Previous Topic:	Compare two different table
Next Topic:	Need SQl Query to retrieve number of notnull rows for each column(5 Merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jun 01 14:46:22 CDT 2026