Oracle – How to View PDBs from HR Schema in Oracle 12c

oracleoracle-12c

I have installed Oracle 12c R2 on my local machine and have created the HR schema using this link : Installing sample schemas

I also gave GRANTS to HR schema to view data dictionary.

GRANT SELECT ANY DICTIONARY TO HR;
GRANT SELECT_CATALOG_ROLE TO HR;

Now, when I connect as SYSDBA, I am able to view PDBS.

SQL> select con_id, dbid, name
     from v$pdbs;

    CON_ID|      DBID|NAME
----------|----------|------------------------
         2|3752719039|PDB$SEED
         3|3679838824|ORCLPDB

However, when logged in as HR, it does not show anything.

SQL> select con_id, dbid, name
    from v$pdbs;

no rows selected

I surely have connected to the same instance.The output of this query is same for both connections.

SQL> select SYS_CONTEXT('USERENV','INSTANCE_NAME') instance  FROM DUAL;

INSTANCE
-----------------
orcl2

What am I missing here?

Best Answer

I assume that the user is a common user connected to the root container.

A common user's CONTAINER_DATA attribute determines which PDBs are visible in container data objects

SQL> alter user c##hr set container_data=all container=current;

User altered.

SQL> conn c##hr
Enter password:
Connected.
SQL> select name from v$pdbs;

NAME
------------------------------
PDB$SEED
ORCLPDB

SQL>
Related Question