atomic_refresh=>TRUE really atomic?
Leo, June 21, 2013 - 10:07 pm UTC
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.
July 01, 2013 - 5:34 pm UTC
give full example to reproduce with.
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>
that was in 9206, I didn't have a 9208 instance around...
Atomic Refresh - Test Case
Leo, June 24, 2013 - 10:14 pm UTC
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
July 01, 2013 - 8:35 pm UTC
I could reproduce that in 9206, but not in current 10gr2, 11gr2, 12cr1 versions.
Looks like it might have been an issue that was corrected 5 major releases ago...
Test Case
Leo, July 01, 2013 - 7:23 pm UTC
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?
So..how to solve it on 9i?
Leo, July 01, 2013 - 9:03 pm UTC
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.
July 01, 2013 - 9:37 pm UTC
I'd try a refresh group. Create a small MV on something and put them together as a refresh group. refresh groups are always atomic.
sorry about my mistake in my example above - that was pretty "not smart" of me :)
Workaround on 9i
Leo, July 01, 2013 - 10:24 pm UTC
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
July 02, 2013 - 4:47 pm UTC
I guess it is 9i bug?
I suspect so, yes.
it would need two MV's at least for the refresh group trick to kick in, definitely.
Not refresh group, but more than one MV matters
Karsten Spang, November 20, 2013 - 9:23 am UTC
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.
Oracle 12.0.2 atomic_refresh vs ORA-12008
A reader, August 23, 2017 - 10:22 am UTC
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.
August 24, 2017 - 1:07 am UTC
Check Mos note 1951899.1 for options.
The size of the mview is not really relevant. What is relevant is
- the duration of the query that is run to derive the mview results
- the tables involved in that query
- the volatility/activity on those tables
- the undo retention settings