Oracle数据库HR schema访问问题：查询v$pdbs无返回值求助

Hey there! I’ve worked through similar Oracle container database issues before—let’s break down why your SELECT name, con_id FROM v$pdbs; query isn’t returning results and fix it step by step:

• Check if you’re connected to the CDB root
  The v$pdbs view only exists in the Container Database (CDB) root (CDB$ROOT). If you’re connected directly to the Pluggable Database (PDB) where your HR schema lives, this view won’t return any data. To confirm your current container:

  SELECT sys_context('userenv', 'con_name') FROM dual;
  

  If the result is a PDB name (like ORCLPDB), switch to the CDB root by connecting as a SYSDBA user (e.g., sqlplus / as sysdba) and re-run the v$pdbs query—you should see all your pluggable databases listed.

• Verify your user has the right permissions
  Even in the CDB root, regular users (including HR) don’t have default access to v$pdbs. You’ll need to grant the SELECT_CATALOG_ROLE role to the user, or run the query as a privileged user like SYS or SYSTEM. For example:

  -- Execute this as SYSDBA
  GRANT SELECT_CATALOG_ROLE TO hr;
  

  Note: HR is typically a PDB-specific user, so you’ll still need to be in the CDB root to query v$pdbs after granting this role.

• Confirm your database is a CDB (not a non-CDB)
  Oracle introduced CDBs and PDBs in version 12c. If you’re running an older version (11g or earlier), or created a non-CDB database, the v$pdbs view doesn’t exist at all. Check your Oracle version with:

  SELECT version FROM v$instance;
  

  If it’s pre-12c, you don’t have PDBs, so this query is irrelevant—you can access the HR schema directly without it.

• Pro tip for working with the HR schema
  Once you’ve identified your PDB from the v$pdbs results (while connected to the CDB root), switch to that PDB to interact with HR:

  -- As SYSDBA in CDB root
  ALTER SESSION SET CONTAINER=your_pdb_name;
  -- Then connect to HR
  CONNECT hr/your_hr_password;
  

  Now you can run your HR schema queries without any container-related hurdles.

内容的提问来源于stack exchange，提问作者Bani Shehi