ORA-23404: refresh group "MY_USER"."MY_TABLE" does not exist

=============

General

=============

Error when running the scheduled refresh for a snapshot

BEGIN
 DBMS_REFRESH.refresh('"MY_USER"."MY_TABLE"');
END;
/
 
 
ORA-23404: refresh group "MY_USER"."MY_TABLE" 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 3
View program sources of error stack?

=============

How to Fix

=============

Solution A

Replace DBMS_REFRESH with DBMS_SNAPSHOT - which does not require group configuration

BEGIN
 DBMS_SNAPSHOT.refresh('"MY_USER"."MY_TABLE"');

END;
/
 
Solution B
Create a Refresh Group. 

And refresh by Refresh group name.

Now:

SELECT rowner, rname, refgroup, job, interval 

FROM USER_REFRESH 

WHERE rname = 'MY_GROUP';

--0 rows selected

Create a new Refresh Group. 

BEGIN

  DBMS_REFRESH.MAKE(name => 'MY_USER.MY_GROUP',

                    list => '', 

    next_date => TRUNC(SYSDATE+1)+0.2/24 , 

    interval => 'TRUNC(SYSDATE+1)+0.2/24 ',

                    implicit_destroy => TRUE, 

    lax => FALSE,

                    job => 299);

  commit;

END;

/

Add Materialized View to the Refresh Group. 

BEGIN

  DBMS_REFRESH.ADD(name => 'MY_USER.MY_GROUP',

                   list => 'MY_USER.MY_TABLE');

  commit;

END;

/

SELECT rowner, rname, refgroup, job, interval 

  FROM USER_REFRESH 

 WHERE rname = 'MY_GROUP';

 

ROWNER  RNAME        REFGROUP  JOB INTERVAL

------- ------------ --------- --- -------------------------

DWNG    MY_GROUP     141       299 TRUNC(SYSDATE+1)+0.5/24

SELECT owner, name, type, rowner, rname, refgroup, job  

  FROM USER_REFRESH_CHILDREN 

WHERE refgroup = 141;

 

OWNER    NAME     TYPE     ROWNER  RNAME        REFGROUP JOB

-------- -------- ---------------- ------------ -------- ---

MY_USER  MY_TABLE SNAPSHOT MY_USER MCCMNC_TABLE 11       299

Join USER_REFRESH  and USER_REFRESH_CHILDREN 

SELECT UR.rowner, UR.rname, URC.name, URC.type, UR.job

  FROM USER_REFRESH UR, USER_REFRESH_CHILDREN URC

 WHERE UR.refgroup = URC.refgroup;

ROWNER     RNAME    NAME      TYPE     JOB

---------- -------- --------- -------- ----

MY_USER    MY_GROUP MY_TABLE  SNAPSHOT 299

 

Fix the broken job

BEGIN

  DBMS_JOB.broken(299, FALSE);

  DBMS_JOB.next_date(299, TRUNC(SYSDATE+1)+0.2/24);

  commit;

END;

/

To test the execution:

BEGIN

  DBMS_JOB.next_date(299, SYSDATE+1/1440);

  commit;

END;

/