Re: materialized view/_simple_view_merging weirdness

From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Thu, 10 Sep 2015 11:29:20 +0800
Message-ID: <CABx0cSVbcpY0nBUfpUa2ygPrh=O4eCfdkvVX2jQYkQCDv3ZOog_at_mail.gmail.com>

Double checking session setting rather than relying on "show parameter". We have identified that spfile actually has _simple_view_merging=FALSE. Note also this is multi-tenant, we restarted PDB yesterday and then started getting this problem.
Maybe a bug?

SQL> SET LINESIZE
360

SQL> 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> _at_pd
simple

Show all parameters and session values from x$ksppi/x$ksppcv...

      INDX I_HEX NAME
VALUE
DESCRIPTION

---------- ----- --------------------------------------------------
------------------------------
----------------------------------------------------------------

---

      2801   AF1 _simple_view_merging
TRUE                           control simple view merging performed by the

optimizer

SQL> --show parameter
"_simple_view_merging";

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> _at_pd
simple

Show all parameters and session values from x$ksppi/x$ksppcv...

      INDX I_HEX NAME
VALUE
DESCRIPTION

---------- ----- --------------------------------------------------
------------------------------
----------------------------------------------------------------

---

      2801   AF1 _simple_view_merging
TRUE                           control simple view merging performed by the

optimizer

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

PL/SQL procedure successfully
completed.

SQL> SQL> On 10 September 2015 at 11:07, Patrick Jolliffe <jolliffe_at_gmail.com> wrote:

> 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:29:20 CEST