3 Replies Latest reply: Mar 31, 2014 11:32 AM by rp0428 RSS

    Strange behaviour with V$CONTAINERS and CDB_TABLESPACES views on Oracle 12c

    user13386370

      Hello,

       

      I am trying to execute a query between V$CONTAINERS and CDB_TABLESPACES views but I have a strange behavior when I am connected with a common user on the root container.

      I have a common user C##SA who has been granted the DBA role in all the containers. Connected to a PDB the following query works:

       

       

      >sqlplus /nolog
      
      SQL*Plus: Release 12.1.0.1.0 Production on Wed Mar 26 10:00:06 2014
      
      Copyright (c) 1982, 2013, Oracle.  All rights reserved.
      
      > connect c##sa@pdb1
      Enter password:
      Connected.
      > show user
      USER is "C##SA"
      > show con_id
      
      CON_ID
      ------------------------------
      3
      > show con_name
      
      CON_NAME
      ------------------------------
      PDB1
      > select * from session_roles;
      
      ROLE
      ------------------------------------------------------------------------------
      DBA
      SELECT_CATALOG_ROLE
      EXECUTE_CATALOG_ROLE
      DELETE_CATALOG_ROLE
      CAPTURE_ADMIN
      EXP_FULL_DATABASE
      IMP_FULL_DATABASE
      DATAPUMP_EXP_FULL_DATABASE
      DATAPUMP_IMP_FULL_DATABASE
      XS_RESOURCE
      GATHER_SYSTEM_STATISTICS
      
      ROLE
      ------------------------------------------------------------------------------
      OPTIMIZER_PROCESSING_RATE
      EM_EXPRESS_BASIC
      EM_EXPRESS_ALL
      SCHEDULER_ADMIN
      HS_ADMIN_SELECT_ROLE
      HS_ADMIN_EXECUTE_ROLE
      XDBADMIN
      XDB_SET_INVOKER
      WM_ADMIN_ROLE
      JAVA_ADMIN
      JAVA_DEPLOY
      
      ROLE
      ------------------------------------------------------------------------------
      OLAP_XS_ADMIN
      OLAP_DBA
      
      24 rows selected.
      
      > SELECT B.NAME
        2  ,      A.TABLESPACE_NAME
        3  FROM CDB_TABLESPACES A
        4  INNER JOIN V$CONTAINERS B
        5     ON B.CON_ID=A.CON_ID
        6  ORDER BY 1,2;
      
      NAME                           TABLESPACE_NAME
      ------------------------------ ------------------------------
      PDB1                           EXAMPLE
      PDB1                           SUPERVISOR
      PDB1                           SYSAUX
      PDB1                           SYSTEM
      PDB1                           TEMP
      PDB1                           USERS
      
      6 rows selected.
      

       

      When I am connected to the root container with this common user I got an error:

       

      Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit P
      With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
      > connect c##sa
      Enter password:
      Connected.
      > show user
      USER is "C##SA"
      > show con_id
      
      CON_ID
      ------------------------------
      1
      > show con_name
      
      CON_NAME
      ------------------------------
      CDB$ROOT
      > select * from session_roles;
      
      ROLE
      -------------------------------------------------------------------------------------
      DBA
      SELECT_CATALOG_ROLE
      EXECUTE_CATALOG_ROLE
      DELETE_CATALOG_ROLE
      CAPTURE_ADMIN
      EXP_FULL_DATABASE
      IMP_FULL_DATABASE
      DATAPUMP_EXP_FULL_DATABASE
      DATAPUMP_IMP_FULL_DATABASE
      XS_RESOURCE
      GATHER_SYSTEM_STATISTICS
      
      ROLE
      -------------------------------------------------------------------------------------
      OPTIMIZER_PROCESSING_RATE
      EM_EXPRESS_BASIC
      EM_EXPRESS_ALL
      SCHEDULER_ADMIN
      HS_ADMIN_SELECT_ROLE
      HS_ADMIN_EXECUTE_ROLE
      XDBADMIN
      XDB_SET_INVOKER
      WM_ADMIN_ROLE
      JAVA_ADMIN
      JAVA_DEPLOY
      
      ROLE
      -------------------------------------------------------------------------------------
      OLAP_XS_ADMIN
      OLAP_DBA
      
      24 rows selected.
      
      > SELECT B.NAME
        2  ,      A.TABLESPACE_NAME
        3  FROM CDB_TABLESPACES A
        4  INNER JOIN V$CONTAINERS B
        5     ON B.CON_ID=A.CON_ID
        6  ORDER BY 1,2;
      SELECT B.NAME
      *
      ERROR at line 1:
      ORA-00604: error occurred at recursive SQL level 1
      ORA-00942: table or view does not exist
      

       

      Furthermore, when I am connected with the SYS user as SYSDBA on the root container I don't have this error:

       

      Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
      With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
      > connect / as sysdba
      Connected.
      > show user
      USER is "SYS"
      > show con_id
      
      CON_ID
      ------------------------------
      1
      > show con_name
      
      CON_NAME
      ------------------------------
      CDB$ROOT
      > SELECT B.NAME
        2  ,      A.TABLESPACE_NAME
        3  FROM CDB_TABLESPACES A
        4  INNER JOIN V$CONTAINERS B
        5     ON B.CON_ID=A.CON_ID
        6  ORDER BY 1,2;
      
      NAME                           TABLESPACE_NAME
      ------------------------------ ------------------------------
      CDB$ROOT                       SYSAUX
      CDB$ROOT                       SYSTEM
      CDB$ROOT                       TEMP
      CDB$ROOT                       UNDOTBS1
      CDB$ROOT                       USERS
      PDB$SEED                       SYSAUX
      PDB$SEED                       SYSTEM
      PDB$SEED                       TEMP
      PDB1                           EXAMPLE
      PDB1                           SUPERVISOR
      PDB1                           SYSAUX
      
      NAME                           TABLESPACE_NAME
      ------------------------------ ------------------------------
      PDB1                           SYSTEM
      PDB1                           TEMP
      PDB1                           USERS
      
      14 rows selected.
      

       

      Do you have any ideas on this behavior?

       

      Thank you in advance,

      Arnaud.

        • 1. Re: Strange behaviour with V$CONTAINERS and CDB_TABLESPACES views on Oracle 12c
          rp0428

          I am trying to execute a query between V$CONTAINERS and CDB_TABLESPACES views but I have a strange behavior when I am connected with a common user on the root container.

          I have a common user C##SA who has been granted the DBA role in all the containers

          . . .

          Do you have any ideas on this behavior?

          Connect C##SA as SYSDBA and the query should work.

           

          There also appear to be some bugs in the code that executes against multiple containers. There is an undocumented function CDB$VIEW that Oracle code uses to execute a query agains each container.

           

          See Solomon's replies in this thread for more details.

          https://community.oracle.com/message/12263085

          • 2. Re: Strange behaviour with V$CONTAINERS and CDB_TABLESPACES views on Oracle 12c
            user13386370

            Hello,

             

            Thank you for your workaround and for the useful link you gave me.

             

            You will find below the execution plan connected with C##SA as sysdba to the root container:

             

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

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

            > connect c##sa as sysdba

            Enter password:

            Connected.

            > show con_name;

             

            CON_NAME

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

            CDB$ROOT

            > EXPLAIN PLAN SET STATEMENT_ID = 'st1a' for SELECT B.NAME

              2      ,      A.TABLESPACE_NAME

              3     FROM CDB_TABLESPACES A

              4      INNER JOIN V$CONTAINERS B

              5         ON B.CON_ID=A.CON_ID

              6      ORDER BY 1,2;

             

            Explained.

             

            > SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', 'st1a', 'all'));

             

            PLAN_TABLE_OUTPUT

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

             

            Plan hash value: 3167149778

             

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

            | Id  | Operation                 | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

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

            |   0 | SELECT STATEMENT          |                  | 10000 |   429K|     0   (0)| 00:00:01 |       |       |        |      |            |

            |   1 |  SORT ORDER BY            |                  | 10000 |   429K|     0   (0)| 00:00:01 |       |       |        |      |            |

            |*  2 |   HASH JOIN               |                  | 10000 |   429K|     0   (0)| 00:00:01 |       |       |        |      |            |

            |*  3 |    FIXED TABLE FULL       | X$CON            |     3 |    42 |     0   (0)| 00:00:01 |       |       |        |      |            |

            |   4 |    PX COORDINATOR         |                  |       |       |            |          |       |       |        |      |            |

            |   5 |     PX SEND QC (RANDOM)   | :TQ10000         | 10000 |   292K|     0   (0)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |

            |   6 |      PX PARTITION LIST ALL|                  | 10000 |   292K|     0   (0)| 00:00:01 |     1 |   254 |  Q1,00 | PCWC |            |

            |   7 |       FIXED TABLE FULL    | X$CDBVW$2b9843c9 | 10000 |   292K|     0   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |

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

             

            Query Block Name / Object Alias (identified by operation id):

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

             

               1 - SEL$CA7F934C

               3 - SEL$CA7F934C / X$CON@SEL$4

               7 - SEL$CA7F934C / DBA_TABLESPACES@SEL$2

             

            Predicate Information (identified by operation id):

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

             

               2 - access("CON_ID"="CON_ID")

               3 - filter("INST_ID"=USERENV('INSTANCE'))

             

            Column Projection Information (identified by operation id):

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

             

               1 - (#keys=2) "NAME"[VARCHAR2,30], "TABLESPACE_NAME"[VARCHAR2,30]

               2 - (#keys=1; rowset=200) "NAME"[VARCHAR2,30], "TABLESPACE_NAME"[VARCHAR2,30]

               3 - "INST_ID"[NUMBER,22], "CON_ID"[NUMBER,22], "NAME"[VARCHAR2,30]

               4 - "TABLESPACE_NAME"[VARCHAR2,30], "CON_ID"[NUMBER,22]

               5 - (#keys=0) "TABLESPACE_NAME"[VARCHAR2,30], "CON_ID"[NUMBER,22]

               6 - (rowset=200) "TABLESPACE_NAME"[VARCHAR2,30], "CON_ID"[NUMBER,22]

               7 - (rowset=200) "TABLESPACE_NAME"[VARCHAR2,30], "CON_ID"[NUMBER,22]

             

            38 rows selected.

             

            You will find below the execution plan connected with C##SA  to the root container:

             

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

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

            > connect c##sa

            Enter password:

            Connected.

            > show con_name;

             

            CON_NAME

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

            CDB$ROOT

            > EXPLAIN PLAN SET STATEMENT_ID = 'st1b' for SELECT B.NAME

              2      ,      A.TABLESPACE_NAME

              3     FROM CDB_TABLESPACES A

              4      INNER JOIN V$CONTAINERS B

              5         ON B.CON_ID=A.CON_ID

              6      ORDER BY 1,2;

             

            Explained.

             

            > SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', 'st1b', 'all'));

             

            PLAN_TABLE_OUTPUT

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

             

            Plan hash value: 1550730524

             

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

            | Id  | Operation                 | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

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

            |   0 | SELECT STATEMENT          |                  |    75 |  3300 |     0   (0)| 00:00:01 |       |       |        |      |            |

            |   1 |  SORT ORDER BY            |                  |    75 |  3300 |     0   (0)| 00:00:01 |       |       |        |      |            |

            |*  2 |   HASH JOIN               |                  |    75 |  3300 |     0   (0)| 00:00:01 |       |       |        |      |            |

            |*  3 |    FIXED TABLE FULL       | X$CON            |     1 |    14 |     0   (0)| 00:00:01 |       |       |        |      |            |

            |   4 |    PX COORDINATOR         |                  |       |       |            |          |       |       |        |      |            |

            |   5 |     PX SEND QC (RANDOM)   | :TQ10000         |   100 |  3000 |     0   (0)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |

            |   6 |      PX PARTITION LIST AND|                  |   100 |  3000 |     0   (0)| 00:00:01 |KEY(AP)|KEY(AP)|  Q1,00 | PCWC |            |

            |   7 |       FIXED TABLE FULL    | X$CDBVW$2b9843c9 |   100 |  3000 |     0   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |

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

             

            Query Block Name / Object Alias (identified by operation id):

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

             

               1 - SEL$9ED8B190

               3 - SEL$9ED8B190 / X$CON@SEL$7

               7 - SEL$9ED8B190 / DBA_TABLESPACES@SEL$3

             

            Predicate Information (identified by operation id):

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

             

               2 - access("CON_ID"="CON_ID")

               3 - filter(("CON_ID"=0 OR "CON_ID"=1) AND "INST_ID"=USERENV('INSTANCE'))

             

            Column Projection Information (identified by operation id):

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

             

               1 - (#keys=2) "NAME"[VARCHAR2,30], "TABLESPACE_NAME"[VARCHAR2,30]

               2 - (#keys=1; rowset=200) "NAME"[VARCHAR2,30], "TABLESPACE_NAME"[VARCHAR2,30]

               3 - "INST_ID"[NUMBER,22], "CON_ID"[NUMBER,22], "NAME"[VARCHAR2,30]

               4 - "TABLESPACE_NAME"[VARCHAR2,30], "CON_ID"[NUMBER,22]

               5 - (#keys=0) "TABLESPACE_NAME"[VARCHAR2,30], "CON_ID"[NUMBER,22]

               6 - (rowset=200) "TABLESPACE_NAME"[VARCHAR2,30], "CON_ID"[NUMBER,22]

               7 - (rowset=200) "TABLESPACE_NAME"[VARCHAR2,30], "CON_ID"[NUMBER,22]

             

            38 rows selected.

             

            The execution plans are slightly different and the second one might have an issue while executing.

             

            Regards,

            Arnaud.

            • 3. Re: Strange behaviour with V$CONTAINERS and CDB_TABLESPACES views on Oracle 12c
              rp0428

              The execution plans are slightly different and the second one might have an issue while executing.

              And did you notice that hidden CDB$VIEW function I mentioned in the plans? 'X$CDBVW$2b9843c9'

               

              That is the function I was talking about and that Solomon discussed in his thread reply in that link.

               

              The 'X$' tables are owned by SYS and can only be directly accessed by SYS (SYSDBA). As mentioned before there are some known issues when some system views are used by that CDB$VIEW function for non-SYSDBA users.

               

              Whether those issues are 'by design' or actual bugs only Oracle knows.