materialized view/_simple_view_merging weirdness

From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Thu, 10 Sep 2015 11:07:08 +0800
Message-ID: <CABx0cSUG3GA+zpbQ-1+4m+Kqu58XMZ1J5zVG6=EjNNeLWtd5EA_at_mail.gmail.com>

Anybody want to hazard a guess at what is going on there, I admit I am puzzled, seems very weird.
Why such a simple mview refresh have a problem? Why does setting a parameter to same value it is currently seem to 'fix' the problem.
Note this is exadata system.

Thanks, Patrick

SQL> select banner from v$version where banner like '%Database%';

BANNER

---

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL> drop materialized view
test_mview;

Materialized view
dropped.

SQL> CREATE MATERIALIZED VIEW
test_mview

  2 AS SELECT
dummy

  3 FROM
dual;

Materialized view
created.

SQL> show parameter
"_simple_view_merging";

PARAMETER_NAME                                               TYPE

VALUE

• -----------

  ---

  ---

_simple_view_merging                                         boolean

TRUE SQL> exec
dbms_mview.refresh('TEST_MVIEW');

BEGIN dbms_mview.refresh('TEST_MVIEW');
END;
*

ERROR at line
1:

ORA-12008: error in materialized view refresh path

ORA-01732: data manipulation operation not legal on this view

ORA-06512: at "SYS.DBMS_SNAPSHOT", line
2821

ORA-06512: at "SYS.DBMS_SNAPSHOT", line
3058

ORA-06512: at "SYS.DBMS_SNAPSHOT", line
3017

ORA-06512: at line
1

SQL> exec
dbms_mview.refresh('TEST_MVIEW');

BEGIN dbms_mview.refresh('TEST_MVIEW');
END;
*

ERROR at line
1:

ORA-12008: error in materialized view refresh path

ORA-01732: data manipulation operation not legal on this view

ORA-06512: at "SYS.DBMS_SNAPSHOT", line
2821

ORA-06512: at "SYS.DBMS_SNAPSHOT", line
3058

ORA-06512: at "SYS.DBMS_SNAPSHOT", line
3017

ORA-06512: at line
1

SQL> ALTER SESSION set
"_simple_view_merging"=TRUE;

Session
altered.

SQL> exec
dbms_mview.refresh('TEST_MVIEW');

PL/SQL procedure successfully
completed.

SQL>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 10 2015 - 05:07:08 CEST