DBMS_MVIEW.REFRESH call cause ORA-01422 error

Question

I performed full database import into existing database with identical structure. After this, call the DBMS_MVIEW.REFRESH procedure cause ORA-01422 error. For example:

begin
 DBMS_MVIEW.REFRESH('SCHEMA_NAME.T_TOP_FRESH_COMMENTS');
end;

Error: ORA-01422 exact fetch returns more than requested number of rows
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2745
ORA-06512: at line 2

Select bellow returns single row:

select * from dba_registered_mviews t where t.name = 'T_TOP_FRESH_COMMENTS'

What do errors cause? How can I fix that? Thanks!

UPD:
Materialized view select doesn’t cause error. The same error repeated for others materialized views. Script for creating view is listed below:

CREATE MATERIALIZED VIEW T_TOP_FRESH_COMMENTS
REFRESH FORCE ON DEMAND
AS
SELECT ROWNUM AS order_comment, id_comment, date_refresh
  FROM (  SELECT c.id_comment, MAX (h.updated) date_refresh
               FROM    t_comment_user c
                 INNER JOIN t_comment_history h  ON c.id_comment = h.id_comment
           WHERE c.published = 'T' AND h.published = 'T' and h.updated is not null GROUP BY c.id_comment
        ORDER BY date_refresh DESC)
 WHERE ROWNUM <= 10;

UPD1: I ran completed refresh and recieved the same error:

begin
  DBMS_MVIEW.REFRESH('SCHEMA_NAME.T_TOP_FRESH_COMMENTS','c');
end;

I recreated materialized view with REFRESH COMPLETE and this didn't help too. I tried to purge view log. It is didn't help:

begin
dbms_mview.purge_log('SCHEMA_NAME.T_COMMENT_HISTORY',99999,'delete');
end;

There must be some sub query in the materialized view script (create materialize view script) which is returning multiple rows where only one is expected. Posting create materialized view code will be helpful. — San
One of the reason can be stale data in materialize view logs (after the import). Try to execute the complete refresh instead of incremental one first. — Dmitry Nikiforov
I've got the same problem yesterday. The following actions did not help: 1) drop & recreate mview log 2) drop & recreate mview 3) select * from sys.snap$, sys.snap_times$, sys.slog$, sys.mlog$ -- no duplicates there — Vladimir Sitnikov
Have you somehow managed to create two materialized views/tables in more than one schema with the same object name? When you search all/user/dba objects, are there only two objects such that you only have a table called T_TOP_FRESH_COMMENTS and a mat view called T_TOP_FRESH_COMMENTS? Or do you have more than those two? — Pete Mahon

Dmitry Remizov Dmitry Remizov · Accepted Answer · 2019-02-09T11:23:57

0

votes

See the Duplicate Entry in SYSTEM.DEF$_DESTINATION Table (Doc ID 2172529.1).

DBMS_MVIEW.REFRESH call cause ORA-01422 error

1 Answers