1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Mutating table issue in oracle 12c environment

Discussion in 'SQL PL/SQL' started by sunil, Mar 16, 2016.

  1. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Hi Zargon,
    We had some testing on few test cases. Here the problem is, the inline function which is the new feature in 12c does not going with a parallel hint.
    Its seems it is the parallel hint that causes the corruption when killing the INSERT with an inline function. Here are my Findings:

    Parallel INSERT Parallel SELECT Causes Mutating Error
    Scenario 1 Y Y Y
    Scenario 2 Y N Y
    Scenario 3 N Y Y
    Scenario 4 N N N

    Please find the steps below which will cause mutating error.

    Run the following SQL to set up test case :
    drop table mutating_table_src;

    create table mutating_table_src
    (num_col number,
    date_col date,
    vc_col varchar2(150 char));

    drop table mutating_table_dest;

    create table mutating_table_dest
    (date_col date);

    begin
    for i in 1..500000
    loop
    insert into mutating_table_src
    values (i,
    sysdate+(i/24),
    'rec '||i);
    commit;
    end loop;
    end;

    Then run this and kill it before it completes (within 15-20 seconds) :

    insert /*+ with_plsql append parallel(8) */ into mutating_table_dest
    with
    function mutating_table_func
    return date is
    dDateCol date;
    begin
    select date_col
    into dDateCol
    from mutating_table_src
    where num_col = 5;
    return dDateCol;
    end;
    select /*+ parallel(8) */ mutating_table_func
    from mutating_table_src;

    Finally, run this INSERT, it should fail with mutating table error :

    insert /*+ append */ into mutating_table_dest
    select /*+ parallel 10 */ date_col
    from mutating_table_src;



    Thanks,
    Sunil
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This code of yours won't run:

    SQL> insert /*+ with_plsql append parallel(8) */ into mutating_table_dest
    2 with
    3 function mutating_table_func
    4 return date is
    5 dDateCol date;
    function mutating_table_func
    *
    ERROR at line 3:
    ORA-06553: PLS-103: Encountered the symbol "end-of-file" when expecting one of
    the following:
    := . ( @ % ; not null range with default character

    I can't see how you report a mutating table error when the insert is so badly written that it cannot execute.
     
  3. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Hi Zargon,
    Here, its a just example for this issue. as we are getting same issue in our environment.
    Here, I forgot to tell you that, the sql's are able to execute with SYSDBA user but not as NON-SYSDBA users. we have tested from TOAD, sql developer also.

    Thanks,
    Sunil Gowda
     
  4. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Hi Zargon,
    I have tested as NON-SYSDBA user running in 12c SQL*Plus. please find the below error.
    this will not work with 11g R2 client.

    FYI:
    [oracle@gtcbi-db-dev ~]$ sqlplus xxxxx/XXXXXX

    SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 17 11:48:56 2016

    Copyright (c) 1982, 2014, Oracle. All rights reserved.

    Last Successful login time: Thu Mar 17 2016 11:41:02 -04:00

    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

    SQL> @mutating_table_test.sql
    SQL> spool mutating_table_test.log
    SQL>
    SQL> drop table mutating_table_src;

    Table dropped.

    SQL>
    SQL> create table mutating_table_src
    2 (num_col number,
    3 date_col date,
    4 vc_col varchar2(150 char));

    Table created.

    SQL>
    SQL> drop table mutating_table_dest;

    Table dropped.

    SQL>
    SQL> create table mutating_table_dest
    2 (date_col date);

    Table created.

    SQL>
    SQL> begin
    2 for i in 1..500000
    3 loop
    4 insert into mutating_table_src
    5 values (i,
    6 sysdate+(i/24),
    7 'rec '||i);
    8 commit;
    9 end loop;
    10 end;
    11 /

    PL/SQL procedure successfully completed.

    SQL>
    SQL> @insert_with_inline_func;
    SQL> insert /*+ with_plsql append parallel(8) */ into mutating_table_dest
    2 with
    3 function mutating_table_func
    4 return date is
    5 dDateCol date;
    6 begin
    7 select date_col
    8 into dDateCol
    9 from mutating_table_src
    10 where num_col = 5;
    11 return dDateCol;
    12 end;
    13 select /*+ parallel(8) */ mutating_table_func
    14 from mutating_table_src;
    15 /
    insert /*+ with_plsql append parallel(8) */ into mutating_table_dest
    *
    ERROR at line 1:
    ORA-12801: error signaled in parallel query server P003
    ORA-04091: table DWADM.MUTATING_TABLE_DEST is mutating, trigger/function may
    not see it


    SQL>
    SQL> insert /*+ append */ into mutating_table_dest
    2 select /*+ parallel 10 */ date_col
    3 from mutating_table_src;
    insert /*+ append */ into mutating_table_dest
    *
    ERROR at line 1:
    ORA-12801: error signaled in parallel query server P000
    ORA-04091: table DWADM.MUTATING_TABLE_DEST is mutating, trigger/function may
    not see it


    SQL>
    SQL> commit;

    Commit complete.

    Thanks,
    Sunil
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Using the 12c client the code runs. Without enabling parallel dml everything runs to completion; attempting to use parallel dml the mutating table error is thrown:

    insert /*+ with_plsql append parallel(8) */ into mutating_table_dest
    *
    ERROR at line 1:
    ORA-12801: error signaled in parallel query server P001
    ORA-04091: table BING.MUTATING_TABLE_DEST is mutating, trigger/function may not
    see it


    SQL>
    SQL> insert /*+ append */ into mutating_table_dest
    2 select /*+ parallel 10 */ date_col
    3 from mutating_table_src;
    insert /*+ append */ into mutating_table_dest
    *
    ERROR at line 1:
    ORA-12801: error signaled in parallel query server P001
    ORA-04091: table BING.MUTATING_TABLE_DEST is mutating, trigger/function may not
    see it


    SQL>

    You might want to file an SR with Oracle support on this.
     
  6. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Sure Zargon. I will do it now and thanks for the update.