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.