5 Replies Latest reply: Sep 10, 2013 12:31 PM by user13386370 RSS

    Oracle 12c - Unexpected result with the insertion of the DBA_VIEWS view

    user13386370

      Hello,

       

      I am trying to insert some fields of the DBA_VIEWS in a table belonging to a common user C##SA from a PDB:

       

      a) Connection to the PDB PDBORCL using the C##SA user:

       

      SQL*Plus: Release 12.1.0.1.0 Production on Tue Sep 10 16:21:39 2013

       

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

       

      Enter password:

      Last Successful login time: Tue Sep 10 2013 16:18:26 +02:00

       

      Connected to:

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

      With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

       

      C##SA@pdborcl 10.09.2013> show con_name

       

      CON_NAME

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

      PDBORCL

      C##SA@pdborcl 10.09.2013> show con_id

       

      CON_ID

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

      3

       

      b) I am creating the user table SA_VIEWS_V

       

      C##SA@pdborcl 10.09.2013> select count(*) from dba_views;

       

        COUNT(*)

      ----------

            6220

       

      1 row selected.

       

      C##SA@pdborcl 10.09.2013> create table sa_views_v (owner varchar2(128),view_name varchar2(128));

       

      Table created.

       

      c) The insert in the table SA_VIEWS_V gave me only 65 rows meanwhile the dba_views contents 6220 rows:

       

      C##SA@pdborcl 10.09.2013> INSERT INTO SA_VIEWS_V (OWNER,VIEW_NAME) SELECT OWNER,VIEW_NAME FROM DBA_VIEWS;

       

      65 rows created.

       

      C##SA@pdborcl 10.09.2013> commit;

       

      Commit complete.

       

      C##SA@pdborcl 10.09.2013> SELECT OWNER,VIEW_NAME FROM DBA_VIEWS;

      ...

      IX

      IX

      IX

      SH

       

      6220 rows selected.

       

      d) You will find above the executed plan of the insert and the single select:

       

      C##SA@pdborcl 10.09.2013> select * from table(dbms_xplan.display_cursor('aj3vkggtvv9d9'));

       

      PLAN_TABLE_OUTPUT

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

       

      SQL_ID  aj3vkggtvv9d9, child number 0

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

      INSERT INTO SA_VIEWS_V (OWNER,VIEW_NAME) SELECT OWNER,VIEW_NAME FROM

      DBA_VIEWS

       

      Plan hash value: 1585970530

       

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

      | Id  | Operation                        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

      |   0 | INSERT STATEMENT                 |                  |       |       |   136 (100)|          |

      |   1 |  LOAD TABLE CONVENTIONAL         |                  |       |       |            |          |

      |*  2 |   FILTER                         |                  |       |       |            |          |

      |*  3 |    HASH JOIN                     |                  |    65 |  6045 |   136   (0)| 00:00:01 |

      |*  4 |     HASH JOIN                    |                  |    65 |  4875 |   132   (0)| 00:00:01 |

      |   5 |      NESTED LOOPS                |                  |       |       |            |          |

      |   6 |       NESTED LOOPS               |                  |    65 |  3315 |   131   (0)| 00:00:01 |

      |   7 |        INDEX FULL SCAN           | I_VIEW1          |    65 |   325 |     1   (0)| 00:00:01 |

      |*  8 |        INDEX RANGE SCAN          | I_OBJ1           |     1 |       |     1   (0)| 00:00:01 |

      |   9 |       TABLE ACCESS BY INDEX ROWID| OBJ$             |     1 |    46 |     2   (0)| 00:00:01 |

      |  10 |      INDEX FULL SCAN             | I_USER2          |   131 |  3144 |     1   (0)| 00:00:01 |

      |  11 |     TABLE ACCESS FULL            | USER$            |   131 |  2358 |     4   (0)| 00:00:01 |

      |* 12 |    TABLE ACCESS FULL             | USER_EDITIONING$ |     1 |     6 |     2   (0)| 00:00:01 |

      |  13 |    NESTED LOOPS SEMI             |                  |     1 |    29 |     2   (0)| 00:00:01 |

      |* 14 |     INDEX SKIP SCAN              | I_USER2          |     1 |    20 |     1   (0)| 00:00:01 |

      |* 15 |     INDEX RANGE SCAN             | I_OBJ4           |     1 |     9 |     1   (0)| 00:00:01 |

      |* 16 |    TABLE ACCESS FULL             | USER_EDITIONING$ |     1 |     6 |     2   (0)| 00:00:01 |

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

       

      Predicate Information (identified by operation id):

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

       

         2 - filter((( IS NULL AND "O"."TYPE#"<>88) OR BITAND("O"."FLAGS",1048576)=1048576 OR

                    BITAND("U"."SPARE1",16)=0 OR (((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND

                    "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_e

                    dition_id'))) OR  IS NOT NULL) AND  IS NOT NULL)))

         3 - access("O"."SPARE3"="U"."USER#")

         4 - access("O"."OWNER#"="U"."USER#")

         8 - access("O"."OBJ#"="V"."OBJ#")

        12 - filter(("TYPE#"=:B1 AND "UE"."USER#"=:B2))

        14 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_editi

                    on_id')))

             filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit

                    ion_id'))))

        15 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

        16 - filter(("UE"."TYPE#"=:B1 AND "UE"."USER#"=:B2))

       

       

      47 rows selected.

       

      C##SA@pdborcl 10.09.2013> select * from table(dbms_xplan.display_cursor('bc4f1jh1snwdp'));

       

      PLAN_TABLE_OUTPUT

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

       

      SQL_ID  bc4f1jh1snwdp, child number 0

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

      SELECT OWNER,VIEW_NAME FROM DBA_VIEWS

       

      Plan hash value: 1508506130

       

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

      | Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |

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

      |   0 | SELECT STATEMENT   |                  |       |       |     1 (100)|       |       |

      |   1 |  PARTITION LIST ALL|                  | 10000 |  1289K|     0   (0)|     1 |     2 |

      |   2 |   FIXED TABLE FULL | X$COMVW$5885ef62 | 10000 |  1289K|     0   (0)|       |       |

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

       

       

      14 rows selected.

       

      Have you an idea about this result?

       

      Thank you in advance,

      Arnaud.