This discussion is archived
5 Replies Latest reply: Sep 10, 2013 10:31 AM by user13386370 RSS

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

user13386370 Newbie
Currently Being Moderated

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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points