=============
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?
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
DBMS_SNAPSHOT.refresh('"MY_USER"."MY_TABLE"');
END;
/
Solution B
Create a Refresh Group.
/
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 selectedCreate 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;
/
