Demystifying the Source of Data in v$database: CDB or PDB in Oracle 21c?

Welcome to the world of Oracle databases, where the Line of Death is a real concept and data accuracy is sacrosanct! As you navigate the vast expanse of database administration, you may have stumbled upon the v$database view, wondering whether the data it provides belongs to the Container Database (CDB) or Pluggable Database (PDB). Fear not, dear reader, for we’re about to embark on a thrilling adventure to unravel the mystery of v$database’s data origin!

What is v$database, anyway?

v$database is a dynamic performance view in Oracle that provides information about the database, including its name, version, and other essential details. It’s a treasure trove of metadata that DBAs and developers alike can tap into to gain valuable insights into the database’s inner workings. But, as we’ll soon discover, not all data in v$database is created equal – or rather, not all data originates from the same source.

The CDB-PDB Conundrum

In Oracle 21c, the introduction of Multitenant architecture brought about the concept of Container Databases (CDBs) and Pluggable Databases (PDBs). A CDB is the top-most container that houses one or more PDBs, each with its own distinct identity. When you create a PDB, it shares the same system resources and tablespaces as the CDB, but has its own set of metadata and system tablespaces.

The question on everyone’s mind is: Which database does the data in v$database belong to – the CDB or PDB? The answer, much like the Oracle database itself, is multifaceted.

Data that belongs to the CDB

Some columns in v$database unequivocally belong to the CDB. These include:

• DBID: The unique identifier of the database, which is the same for the CDB and all its PDBs.
• NAME: The name of the CDB, which is the same for all PDBs.
• VERSION: The version of the Oracle database software, which applies to the entire CDB.
• CREATED: The timestamp when the CDB was created.

These columns provide information that is applicable to the CDB as a whole, rather than individual PDBs. When you query v$database, you’ll see the same values for these columns regardless of the PDB you’re connected to.

Data that belongs to the PDB

Other columns in v$database, however, are specific to the PDB you’re currently connected to. These include:

• CON_DBID: The container database ID, which is unique to each PDB.
• CON_UID: The container unique identifier, which is also unique to each PDB.
• PDB_NAME: The name of the PDB, which is distinct from the CDB’s name.
• PDB_USER_TABLESPACE: The default tablespace for user data in the PDB.

These columns provide metadata specific to the PDB, which may differ from one PDB to another within the same CDB.

The Hybrid Columns

And then, there are columns that blur the lines between CDB and PDB. These hybrid columns may contain data that is applicable to both or depends on the context. For example:

• INSTANCE_NUMBER: The instance number of the Oracle instance, which can be shared across multiple PDBs in a RAC environment.
• SESSIONS_MAX: The maximum number of sessions allowed, which may be configured at the CDB level but can be overridden at the PDB level.

When querying v$database, you may need to consider the context in which the data is being retrieved to accurately interpret these hybrid columns.

Best Practices for Working with v$database

To ensure you’re getting the most out of v$database, follow these best practices:

1. Connect to the correct database: Make sure you’re connected to the desired PDB or CDB when querying v$database, as the data may vary depending on the context.
2. Understand the column’s scope: Familiarize yourself with the columns in v$database and their scope, whether they belong to the CDB, PDB, or are hybrid.
3. Use the correct syntax: When querying v$database, use the correct syntax to avoid errors and ensure you’re retrieving the intended data.
4. Be mindful of RAC environments: In Real Application Clusters (RAC) environments, be aware that some columns may be shared across instances or nodes.

Conclusion

In conclusion, the data obtained from v$database in Oracle 21c can originate from both the CDB and PDB, depending on the column. By understanding the scope and context of each column, you’ll be better equipped to navigate the complexities of Multitenant architecture and make informed decisions about your database management. Remember, with great power comes great responsibility – so wield your v$database queries wisely!

  
  SELECT * FROM v$database;

  /* Output: */

  DBID        NAME          VERSION       CREATED
  ----------  ---------  -------------  ----------
  123456789  CDB1          21.0.0.0.0  2022-01-01 00:00:00
  CON_DBID    CON_UID      PDB_NAME
  ----------  ----------  ---------
  123456789  123456789  PDB1
  

Column Name	Description	Scope
DBID	Database ID	CDB
CON_DBID	Container database ID	PDB
INSTANCE_NUMBER	Instance number	Hybrid

Stay tuned for more exciting adventures in the world of Oracle databases!

Here is the HTML code with 5 Questions and Answers about “Is the data obtained from v$database in Oracle21c CDB or PDB?”:

Frequently Asked Question

If you’re unsure about where the data comes from in v$database in Oracle21c, you’re in the right place! Here are some FAQs to get you started.