1

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?

2

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>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.