Tom,
if we set atomic_refresh=>TRUE, so we have an atomic transaction (delete+insert), why do I get an emtpy table when the insert fails? I thought if the insert fails, the delete would be rollbacked.
Or am I doing something wrong?
I'm using dbms_mview.refresh('<table>', method => 'C', atomic_refresh => true) on Oracle 9.2.0.8.0.

SQL> create table t ( x int primary key, y int );

Table created.

SQL>
SQL> create materialized view mv
  2  as
  3  select * from t;

Materialized view created.

SQL>
SQL> alter table mv add constraint check_y check(y>0);

Table altered.

SQL>
SQL> insert into t values ( 1, 1 );

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_mview.refresh( 'MV', method=>'C',atomic_refresh=>true);

PL/SQL procedure successfully completed.

SQL> select * from mv;

         X          Y
---------- ----------
         1          1

SQL> insert into t values ( 1, -1 );
insert into t values ( 1, -1 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.SYS_C003209) violated


SQL> exec dbms_mview.refresh( 'MV', method=>'C',atomic_refresh=>true);

PL/SQL procedure successfully completed.

SQL> select * from mv;

         X          Y
---------- ----------
         1          1

SQL>
SQL>

Here is a test case, about my previous post:

drop materialized view mv_test;
Materialized view dropped

create or replace view v_test as select * from dual;
View created

create materialized view mv_test as select * from v_test;
Materialized view created

select count(*) from mv_test;
  COUNT(*)
----------
         1

exec dbms_mview.refresh('MV_TEST', atomic_refresh => true);
PL/SQL procedure successfully completed

select count(*) from mv_test;
  COUNT(*)
----------
         1

drop view v_test;
View dropped

select count(*) from mv_test; 
  COUNT(*)
----------
         1

exec dbms_mview.refresh('MV_TEST', atomic_refresh => true);
 
begin dbms_mview.refresh('MV_TEST', atomic_refresh => true); end; 
ORA-12008: error en la ruta de acceso de refrescamiento de la vista materializada
ORA-00942: la tabla o vista no existe
ORA-00942: la tabla o vista no existe
ORA-06512: en "SYS.DBMS_SNAPSHOT", línea 820
ORA-06512: en "SYS.DBMS_SNAPSHOT", línea 877
ORA-06512: en "SYS.DBMS_SNAPSHOT", línea 858
ORA-06512: en línea 2

select count(*) from mv_test;
  COUNT(*)
----------
         0

I had already posted a test case.

Anyway, the one you posted can also be used to show what I mean, but the refresh should fail to prove my point, not the insert on the base table.
I have modified the insert for showing it to you:

create table t ( x int primary key, y int );
Table created

create materialized view mv as select * from t;
Materialized view created

alter table mv add constraint check_y check(y>0);
Table altered

insert into t values ( 1, 1 );
1 row inserted

commit;
Commit complete

exec dbms_mview.refresh( 'MV', method=>'C',atomic_refresh=>true);
PL/SQL procedure successfully completed

select * from mv;                                      X                                       Y
--------------------------------------- ---------------------------------------
                                      1                                       1

insert into t values ( 2, -1 ); --changed to avoid unique restricion error
1 row inserted

commit;
Commit complete

exec dbms_mview.refresh( 'MV', method=>'C',atomic_refresh=>true);
 
begin dbms_mview.refresh( 'MV', method=>'C',atomic_refresh=>true); end;
 
ORA-12008: error en la ruta de acceso de refrescamiento de la vista materializada
ORA-02290: restricción de control (FACT.CHECK_Y) violada
ORA-06512: en "SYS.DBMS_SNAPSHOT", línea 820
ORA-06512: en "SYS.DBMS_SNAPSHOT", línea 877
ORA-06512: en "SYS.DBMS_SNAPSHOT", línea 858
ORA-06512: en línea 2

select * from mv;                                 X                                       Y
--------------------------------------- ---------------------------------------

So, at least under my Oracle version, if the refresh process fails, the materialized view ends empty.
Which means the delete was commited, so there is no-atomic-transactional behaviour.
Any help about it?

Thanks a lot for checking it and answering.
I have tested on 10g and you are right, it works atomic as I would have expected.
So, the atomic_refresh parameter doesn't work as it should on 9i.

Any idea or suggestion on how to make a complete atomic refresh under 9i?

In the particular case I need it, it is really a problem if the materialized view gets empty at any moment, because it's a lookup table for a frequent automatic job.

It worked with a refresh group!
But it works only if the refresh group has more then one view, as you suggested.
I guess it is 9i bug?
Thanks a lot anyway!

With only one view on the refresh group it doesn't work;

create table t ( x int primary key, y int );
Table created

create materialized view mv as select * from t;
Materialized view created

alter table mv add constraint check_y check(y>0); 
Table altered

exec dbms_refresh.make('G', 'MV', sysdate, null);
 PL/SQL procedure successfully completed

insert into t values ( 1, 1 ); 
1 row inserted
commit;
Commit complete

exec dbms_refresh.refresh('G'); 
PL/SQL procedure successfully completed
select * from mv;
 
                                      X                                       Y
--------------------------------------- ---------------------------------------
                                      1                                       1

insert into t values ( 2, -1 );
1 row inserted

commit;
Commit complete

exec dbms_refresh.refresh('G');
begin dbms_refresh.refresh('G'); end;

ORA-12008: error en la ruta de acceso de refrescamiento de la vista materializada
ORA-02290: restricción de control (PMOV.CHECK_Y) violada
ORA-06512: en "SYS.DBMS_SNAPSHOT", línea 820
ORA-06512: en "SYS.DBMS_SNAPSHOT", línea 877
ORA-06512: en "SYS.DBMS_IREFRESH", línea 683
ORA-06512: en "SYS.DBMS_REFRESH", línea 195
ORA-06512: en línea 2

select * from mv;
                                      X                                       Y
--------------------------------------- ---------------------------------------

Having a second materialized view on the group, it works:

create table t ( x int primary key, y int );
Table created

create materialized view mv as select * from t; 
Materialized view created

create materialized view mv2 as select * from dual; 
Materialized view created

alter table mv add constraint check_y check(y>0); 
Table altered

exec dbms_refresh.make('G', 'MV, MV2', sysdate, null); 
PL/SQL procedure successfully completed

insert into t values ( 1, 1 ); 
1 row inserted

commit; 
Commit complete

exec dbms_refresh.refresh('G'); 
PL/SQL procedure successfully completed

select * from mv; 
                                      X                                       Y
--------------------------------------- ---------------------------------------
                                      1                                       1
insert into t values ( 2, -1 );
1 row inserted

commit; 
Commit complete

exec dbms_refresh.refresh('G');
 
begin dbms_refresh.refresh('G'); end;
 
ORA-12008: error en la ruta de acceso de refrescamiento de la vista materializada
ORA-02290: restricción de control (PMOV.CHECK_Y) violada
ORA-06512: en "SYS.DBMS_SNAPSHOT", línea 820
ORA-06512: en "SYS.DBMS_SNAPSHOT", línea 877
ORA-06512: en "SYS.DBMS_IREFRESH", línea 683
ORA-06512: en "SYS.DBMS_REFRESH", línea 195
ORA-06512: en línea 2
select * from mv;
 
                                      X                                       Y
--------------------------------------- ---------------------------------------
                                      1                                       1

I remember this issue from 8i. I guess that it was still present in 9. "Atomic" simply did not work unless at least two mviews were refreshed in one operation. You can achieve this by either using a group of at least two members, or by specifying more than one mview in the argument to REFRESH.

Attempting to refresh a mview over 179 row remote table:
DBMS_MVIEW.REFRESH(LIST=>'remote_db_mytable_mv',atomic_refresh=>false, PARALLELISM=>32,method=>'C');
fails repeatably with

"ORA-12008: error in materialized view refresh path
ORA-01555: snapshot too old: rollback segment number 9 with name ""_SYSSMU9_1170630825$"" too small
ORA-02063: preceding line from SPS_TMP_IP
ORA-06512: at ""SYS.DBMS_SNAPSHOT"", line 2821
ORA-06512: at ""SYS.DBMS_SNAPSHOT"", line 3058
ORA-06512: at ""SYS.DBMS_SNAPSHOT"", line 3017

however
DBMS_MVIEW.REFRESH(LIST=>'remote_db_mytable_mv',atomic_refresh=>true, PARALLELISM=>32,method=>'C');
above executes correctly in 1.3s and afterwards both methods (atomic_refresh = True and atomic_refresh = False) work correctly again. The remote table holds 179 rows, is not in use. Parallelism set to 32 has no sense for remote table accessible via dblink and should have not been used IMHO, was kept only to reproduce error.