Skip Headers
Oracle® Database Administrator's Guide
12c Release 1 (12.1)

E17636-21
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

43 Viewing Information About CDBs and PDBs with SQL*Plus

This chapter contains the following topics:

About CDB and PDB Information in Views

In a multitenant container database (CDB), the metadata for data dictionary tables and view definitions is stored only in the root. However, each pluggable database (PDB) has its own set of data dictionary tables and views for the database objects contained in the PDB.

Because each PDB can contain different data and schema objects, PDBs can display different information in data dictionary views, even when querying the same data dictionary view in each PDB. For example, the information about tables displayed in the DBA_TABLES view can be different in two different PDBs, because the PDBs can contain different tables. An internal mechanism called a metadata link enables a PDB to access the metadata for these views in the root.

If a dictionary table stores information that pertains to the CDB as a whole, instead of for each PDB, then both the metadata and the data displayed in a data dictionary view are stored in the root. For example, Automatic Workload Repository (AWR) data is stored in the root and displayed in some data dictionary views, such as the DBA_HIST_ACTIVE_SESS_HISTORY view. An internal mechanism called an object link enables a PDB to access both the metadata and the data for these types of views in the root.

See Also:

Oracle Database Concepts for more information about dictionary access in containers, metadata links, and object links

About Viewing Information When the Current Container Is the Root

When the current container is the root, a common user can view data dictionary information for the root and for PDBs by querying container data objects. A container data object is a table or view that can contain data pertaining to the following:

  • One or more containers

  • The CDB as a whole

  • One or more containers and the CDB as a whole

Container data objects include V$, GV$, CDB_, and some Automatic Workload Repository DBA_HIST* views. A common user's CONTAINER_DATA attribute determines which PDBs are visible in container data objects.

In a CDB, for every DBA_ view, there is a corresponding CDB_ view. All CDB_ views are container data objects, but most DBA_ views are not.

Each container data object contains a CON_ID column that identifies the container for each row returned. Table 43-1 describes the meanings of the values in the CON_ID column.

Table 43-1 CON_ID Column in Container Data Objects

Value in CON_ID Column Description

0

The data pertains to the entire CDB

1

The data pertains to the root

2

The data pertains to the seed

3 - 254

The data pertains to a PDB

Each PDB has its own container ID.


The following views behave differently from other [G]V$ views:

  • [G]V$SYSSTAT

  • [G]V$SYS_TIME_MODEL

  • [G]V$SYSTEM_EVENT

  • [G]V$SYSTEM_WAIT_CLASS

When queried from the root, these views return instance-wide data, with 0 in the CON_ID column for each row returned. However, you can query equivalent views that behave the same as other container data objects. The following views can return specific data for each container in a CDB: [G]V$CON_SYSSTAT, [G]V$CON_SYS_TIME_MODEL, [G]V$CON_SYSTEM_EVENT, and [G]V$CON_SYSTEM_WAIT_CLASS.

Note:

  • When querying a container data object, the data returned depends on whether PDBs are open and on the privileges granted to the user running the query.

  • In an Oracle Real Application Clusters (Oracle RAC) environment, the data returned by container data objects might vary based on the instance to which a session is connected.

  • In a non-CDB, all CON_ID columns in container data objects are 0 (zero).

See Also:

About Viewing Information When the Current Container Is a PDB

When the current container is a PDB, a user can view data dictionary information for the current PDB only. To an application connected to a particular PDB, the data dictionary appears as it would for a non-CDB. The data dictionary only shows information related to the PDB. Also, in a PDB, CDB_ views only show information about database objects visible through the corresponding DBA_ view.

Views for a CDB

Table 43-2 describes data dictionary views that are useful for monitoring a CDB and its PDBs.

Table 43-2 Views for a CDB

View Description More Information

Container data objects, including:

  • V$ views

  • GV$ views

  • CDB_ views

  • DBA_HIST* views

Container data objects can display information about multiple PDBs. Each container data object includes a CON_ID column to identify containers.

There is a CDB_ view for each corresponding DBA_ view.

"Querying Container Data Objects"

Oracle Database Security Guide

{CDB|DBA}_PDBS

Displays information about the PDBs associated with the CDB, including the status of each PDB.

"Viewing Information About PDBs"

Oracle Database Reference

CDB_PROPERTIES

Displays the permanent properties of each container in a CDB.

Oracle Database Reference

{CDB|DBA}_PDB_HISTORY

Displays the history of each PDB.

Oracle Database Reference

{CDB|DBA}_CONTAINER_DATA

Displays information about the user-level and object-level CONTAINER_DATA attributes specified in the CDB.

Oracle Database Reference

{USER|ALL|DBA|CDB}_OBJECTS

Displays information about database objects, and the SHARING column shows whether a database object is a metadata-linked object, an object-linked object, or a standalone object that is not linked to another object.

Oracle Database Reference

{ALL|DBA|CDB}_SERVICES

Displays information about database services, and the PDB column shows the name of the PDB associated with each service.

Oracle Database Reference

{USER|ALL|DBA|CDB}_VIEWS

{USER|ALL|DBA|CDB}_TABLES

The CONTAINER_DATA column shows whether the view or table is a container data object.

 

{USER|ALL|DBA|CDB}_USERS

The COMMON column shows whether a user is a common user or a local user.

 

{USER|ALL|DBA|CDB}_ROLES

{USER|ALL|DBA|CDB}_COL_PRIVS

{USER|ALL}_COL_PRIVS_MADE

{USER|ALL}_COL_PRIVS_RECD

{USER|ALL}_TAB_PRIVS_MADE

{USER|ALL}_TAB_PRIVS_RECD

{USER|DBA|CDB}_SYS_PRIVS

{USER|DBA|CDB}_ROLE_PRIVS

ROLE_TAB_PRIVS

ROLE_SYS_PRIVS

The COMMON column shows whether a role or privilege is commonly granted or locally granted.

 

{USER|ALL|DBA|CDB}_ARGUMENTS

{USER|ALL|DBA|CDB}_CLUSTERS

{USER|ALL|DBA|CDB}_CONSTRAINTS

{ALL|DBA|CDB}_DIRECTORIES

{USER|ALL|DBA|CDB}_IDENTIFIERS

{USER|ALL|DBA|CDB}_LIBRARIES

{USER|ALL|DBA|CDB}_PROCEDURES

{USER|ALL|DBA|CDB}_SOURCE

{USER|ALL|DBA|CDB}_SYNONYMS

{USER|ALL|DBA|CDB}_VIEWS

The ORIGIN_CON_ID column shows the ID of the container from which the row originates.

 

[G]V$DATABASE

Displays information about the database from the control file. If the database is a CDB, then CDB-related information is included.

"Determining Whether a Database Is a CDB"

Oracle Database Reference

[G]V$CONTAINERS

Displays information about the containers associated with the current CDB, including the root and all PDBs.

"Viewing Information About the Containers in a CDB"

Oracle Database Reference

[G]V$PDBS

Displays information about the PDBs associated with the current CDB, including the open mode of each PDB.

"Viewing the Open Mode of Each PDB"

Oracle Database Reference

[G]V$SYSTEM_PARAMETER

[G]V$PARAMETER

Displays information about initialization parameters, and the ISPDB_MODIFIABLE column shows whether a parameter can be modified for a PDB.

"Listing the Initialization Parameters That Are Modifiable in PDBs"

Oracle Database Reference


Determining Whether a Database Is a CDB

You can query the CDB column in the V$DATABASE view to determine whether a database is a CDB or a non-CDB. The CDB column returns YES if the current database is a CDB or NO if the current database is a non-CDB.

To determine whether a database is a CDB: 

  1. In SQL*Plus, connect to the database as an administrative user.

  2. Query the V$DATABASE view.

Example 43-1 Determining Whether a Database is a CDB

SELECT CDB FROM V$DATABASE;

Sample output:

CDB
---
YES

Viewing Information About the Containers in a CDB

The V$CONTAINERS view provides information about all of the containers in a CDB, including the root and all PDBs. To view this information, the query must be run by a common user whose current container is the root. When the current container is a PDB, this view only shows information about the current PDB.

To view information about the containers in a CDB: 

  1. In SQL*Plus, ensure that the current container is the root.

    See "Accessing a Container in a CDB with SQL*Plus".

  2. Query the V$CONTAINERS view.

Example 43-2 Viewing Identifying Information About Each Container in a CDB

COLUMN NAME FORMAT A8

SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

Sample output:

NAME         CON_ID       DBID    CON_UID GUID
-------- ---------- ---------- ---------- --------------------------------
CDB$ROOT          1  659189539          1 C091A6F89C7572A1E0436797E40AC78D
PDB$SEED          2 4026479912 4026479912 C091AE9C00377591E0436797E40AC138
HRPDB             3 3718888687 3718888687 C091B6B3B53E7834E0436797E40A9040
SALESPDB          4 2228741407 2228741407 C091FA64EF8F0577E0436797E40ABE9F

Viewing Information About PDBs

The CDB_PDBS view and DBA_PDBS view provide information about the PDBs associated with a CDB, including the status of each PDB. To view this information, the query must be run by a common user whose current container is the root. When the current container is a PDB, all queries on these views return no results.

To view information about PDBs: 

  1. In SQL*Plus, ensure that the current container is the root.

    See "Accessing a Container in a CDB with SQL*Plus".

  2. Query the CDB_PDBS or DBA_PDBS view.

Example 43-3 Viewing Container ID, Name, and Status of Each PDB

COLUMN PDB_NAME FORMAT A15
 
SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;

Sample output:

    PDB_ID PDB_NAME        STATUS
---------- --------------- -------------
         2 PDB$SEED        NORMAL
         3 HRPDB           NORMAL
         4 SALESPDB        NORMAL

Viewing the Open Mode of Each PDB

The V$PDBS view provides information about the PDBs associated with the current database instance. You can query this view to determine the open mode of each PDB. For each PDB that is open, this view can also show when the PDB was last opened. A common user can query this view when the current container is the root or a PDB. When the current container is a PDB, this view only shows information about the current PDB.

To view the open status of each PDB: 

  1. In SQL*Plus, access a container.

    See "Accessing a Container in a CDB with SQL*Plus".

  2. Query the V$PDBS view.

Example 43-4 Viewing the Name and Open Mode of Each PDB

COLUMN NAME FORMAT A15
COLUMN RESTRICTED FORMAT A10
COLUMN OPEN_TIME FORMAT A30
 
SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;

Sample output:

NAME            OPEN_MODE  RESTRICTED OPEN_TIME
--------------- ---------- ---------- ------------------------------
PDB$SEED        READ ONLY  NO         21-MAY-12 12.19.54.465 PM
HRPDB           READ WRITE NO         21-MAY-12 12.34.05.078 PM
SALESPDB        MOUNTED    NO         22-MAY-12 10.37.20.534 AM

Querying Container Data Objects

In the root, container data objects can show information about database objects (such as tables and users) contained in the root and in PDBs. Access to PDB information is controlled by the common user's CONTAINER_DATA attribute. For example, CDB_ views are container data objects. See "About Viewing Information When the Current Container Is the Root" and Oracle Database Security Guide for more information about container data objects.

Each container data object contains a CON_ID column that shows the container ID of each PDB in the query results. You can view the PDB name for a container ID by querying the DBA_PDBS view.

To use container data objects to show information about multiple PDBs: 

  1. In SQL*Plus, ensure that the current container is the root.

    See "Accessing a Container in a CDB with SQL*Plus".

  2. Query the container data object to show the desired information.

Note:

When a query contains a join of a container data object and a non-container data object, and the current container is the root, the query returns data for the entire CDB only (CON_ID = 0).

See Also:

This section contains the following examples:

Example 43-5 Showing the Tables Owned by Specific Schemas in Multiple PDBs

This example queries the DBA_PDBS view and the CDB_TABLES view from the root to show the tables owned by hr user and oe user in the PDBs associated with the CDB. This query returns only rows where the PDB has an ID greater than 2 (p.PDB_ID > 2) to avoid showing the users in the root and seed.

COLUMN PDB_NAME FORMAT A15
COLUMN OWNER FORMAT A15
COLUMN TABLE_NAME FORMAT A30
 
SELECT p.PDB_ID, p.PDB_NAME, t.OWNER, t.TABLE_NAME 
  FROM DBA_PDBS p, CDB_TABLES t 
  WHERE p.PDB_ID > 2 AND
        t.OWNER IN('HR','OE') AND
        p.PDB_ID = t.CON_ID
  ORDER BY p.PDB_ID;

Sample output:

    PDB_ID PDB_NAME        OWNER           TABLE_NAME
---------- --------------- --------------- ------------------------------
         3 HRPDB           HR              COUNTRIES
         3 HRPDB           HR              JOB_HISTORY
         3 HRPDB           HR              EMPLOYEES
         3 HRPDB           HR              JOBS
         3 HRPDB           HR              DEPARTMENTS
         3 HRPDB           HR              LOCATIONS
         3 HRPDB           HR              REGIONS
         4 SALESPDB        OE              PRODUCT_INFORMATION
         4 SALESPDB        OE              INVENTORIES
         4 SALESPDB        OE              ORDERS
         4 SALESPDB        OE              ORDER_ITEMS
         4 SALESPDB        OE              WAREHOUSES
         4 SALESPDB        OE              CUSTOMERS
         4 SALESPDB        OE              SUBCATEGORY_REF_LIST_NESTEDTAB
         4 SALESPDB        OE              PRODUCT_REF_LIST_NESTEDTAB
         4 SALESPDB        OE              PROMOTIONS
         4 SALESPDB        OE              PRODUCT_DESCRIPTIONS

This sample output shows the PDB hrpdb has tables in the hr schema and the PDB salespdb has tables in the oe schema.

Example 43-6 Showing the Users in Multiple PDBs

This example queries the DBA_PDBS view and the CDB_USERS view from the root to show the users in each PDB. The query uses p.PDB_ID > 2 to avoid showing the users in the root and the seed.

COLUMN PDB_NAME FORMAT A15
COLUMN USERNAME FORMAT A30
 
SELECT p.PDB_ID, p.PDB_NAME, u.USERNAME 
  FROM DBA_PDBS p, CDB_USERS u
  WHERE p.PDB_ID > 2 AND
        p.PDB_ID = u.CON_ID
  ORDER BY p.PDB_ID;

Sample output:

    PDB_ID PDB_NAME        USERNAME
---------- --------------- ------------------------------
         .
         .
         .
         3 HRPDB           HR
         3 HRPDB           OLAPSYS
         3 HRPDB           MDSYS
         3 HRPDB           ORDSYS
         .
         .
         .
         4 SALESPDB        OE
         4 SALESPDB        CTXSYS
         4 SALESPDB        MDSYS
         4 SALESPDB        EXFSYS
         4 SALESPDB        OLAPSYS
         .
         .
         .

Example 43-7 Showing the Data Files for Each PDB in a CDB

This example queries the DBA_PDBS and CDB_DATA_FILES views to show the name and location of each data file for all of the PDBs in a CDB, including the seed.

COLUMN PDB_ID FORMAT 999
COLUMN PDB_NAME FORMAT A8
COLUMN FILE_ID FORMAT 9999
COLUMN TABLESPACE_NAME FORMAT A10
COLUMN FILE_NAME FORMAT A45

SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
  FROM DBA_PDBS p, CDB_DATA_FILES d
  WHERE p.PDB_ID = d.CON_ID
  ORDER BY p.PDB_ID;

Sample output:

PDB_ID PDB_NAME FILE_ID TABLESPACE FILE_NAME
------ -------- ------- ---------- ---------------------------------------------
     2 PDB$SEED       6 SYSAUX     /disk1/oracle/dbs/pdbseed/cdb1_ax.f
     2 PDB$SEED       5 SYSTEM     /disk1/oracle/dbs/pdbseed/cdb1_db.f
     3 HRPDB          9 SYSAUX     /disk1/oracle/dbs/hrpdb/hrpdb_ax.f
     3 HRPDB          8 SYSTEM     /disk1/oracle/dbs/hrpdb/hrpdb_db.f
     3 HRPDB         13 USER       /disk1/oracle/dbs/hrpdb/hrpdb_usr.dbf
     4 SALESPDB      15 SYSTEM     /disk1/oracle/dbs/salespdb/salespdb_db.f
     4 SALESPDB      16 SYSAUX     /disk1/oracle/dbs/salespdb/salespdb_ax.f
     4 SALESPDB      18 USER       /disk1/oracle/dbs/salespdb/salespdb_usr.dbf

Example 43-8 Showing the Temp Files in a CDB

This example queries the CDB_TEMP_FILES view to show the name and location of each temp file in a CDB, as well as the tablespace that uses the temp file.

COLUMN CON_ID FORMAT 999
COLUMN FILE_ID FORMAT 9999
COLUMN TABLESPACE_NAME FORMAT A15
COLUMN FILE_NAME FORMAT A45

SELECT CON_ID, FILE_ID, TABLESPACE_NAME, FILE_NAME
  FROM CDB_TEMP_FILES
  ORDER BY CON_ID;

Sample output:

CON_ID FILE_ID TABLESPACE_NAME FILE_NAME
------ ------- --------------- ---------------------------------------------
     1       1 TEMP            /disk1/oracle/dbs/t_tmp1.f
     2       2 TEMP            /disk1/oracle/dbs/pdbseed/t_tmp1.f
     3       3 TEMP            /disk1/oracle/dbs/hrpdb/t_hrpdb_tmp1.f
     4       4 TEMP            /disk1/oracle/dbs/salespdb/t_salespdb_tmp1.f

Example 43-9 Showing the Services Associated with PDBs

This example queries the CDB_SERVICES view to show the PDB name, network name, and container ID of each service associated with a PDB.

COLUMN NETWORK_NAME FORMAT A30
COLUMN PDB FORMAT A15
COLUMN CON_ID FORMAT 999

SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES
  WHERE PDB IS NOT NULL AND
        CON_ID > 2
  ORDER BY PDB;

Sample output:

PDB             NETWORK_NAME                   CON_ID
--------------- ------------------------------ ------
HRPDB           hrpdb.example.com                   3
SALESPDB        salespdb.example.com                4

Determining the Current Container ID or Name

This section describes determining your current container ID or container name in a CDB.

To determine the current container ID: 

  • Run the following SQL*Plus command:

    SHOW CON_ID
    

To determine the current container name: 

  • Run the following SQL*Plus command:

    SHOW CON_NAME
    

In addition, you can use the functions listed in Table 43-3 to determine the container ID of a container.

Table 43-3 Functions That Return the Container ID of a Container

Function Description

CON_NAME_TO_ID('container_name')

Returns the container ID based on the container's name.

CON_DBID_TO_ID(container_dbid)

Returns the container ID based on the container's DBID.

CON_UID_TO_ID(container_uid)

Returns the container ID based on the container's unique identifier (UID).

CON_GUID_TO_ID(container_guid)

Returns the container ID based on the container's globally unique identifier (GUID).


The V$CONTAINERS view shows the name, DBID, UID, and GUID for each container in a CDB.

Example 43-10 Returning the Container ID Based on the Container Name

SELECT CON_NAME_TO_ID('HRPDB') FROM DUAL;

Example 43-11 Returning the Container ID Based on the Container DBID

SELECT CON_DBID_TO_ID(2226957846) FROM DUAL;

Listing the Initialization Parameters That Are Modifiable in PDBs

In a CDB, some initialization parameters apply to the root and to all of the PDBs. When such an initialization parameter is changed, it affects the entire CDB.

You can set other initialization parameters to different values in each container. For example, you might have a parameter set to one value in the root, set to another value in one PDB, and set to yet another value in a second PDB.

The query in this section lists the initialization parameters that you can set independently in each PDB.

To list the initialization parameters that are modifiable in each container: 

  1. In SQL*Plus, access a container.

    See "Accessing a Container in a CDB with SQL*Plus".

  2. Run the following query:

    SELECT NAME FROM V$SYSTEM_PARAMETER
      WHERE ISPDB_MODIFIABLE = 'TRUE'
      ORDER BY NAME;
    

If an initialization parameter listed by this query is not set independently for a PDB, then the PDB inherits the parameter value of the root.

Viewing the History of PDBs

The CDB_PDB_HISTORY view shows the history of the PDBs in a CDB. It provides information about when and how each PDB was created and other information about each PDB's history.

To view the history of each PDB: 

  1. In SQL*Plus, ensure that the current container is the root.

    See "Accessing a Container in a CDB with SQL*Plus".

  2. Query CDB_PDB_HISTORY view.

Example 43-12 Viewing the History of PDBs

This example shows the following information about each PDB's history:

  • The DB_NAME field shows the CDB that contained the PDB.

  • The CON_ID field shows the container ID of the PDB.

  • The PDB_NAME field shows the name of the PDB in one of its incarnations.

  • The OPERATION field shows the operation performed in the PDB's history.

  • The OP_TIMESTAMP field shows the date on which the operation was performed.

  • If the PDB was cloned in an operation, then the CLONED_FROM_PDB field shows the PDB from which the PDB was cloned.

COLUMN DB_NAME FORMAT A10
COLUMN CON_ID FORMAT 999
COLUMN PDB_NAME FORMAT A15
COLUMN OPERATION FORMAT A16
COLUMN OP_TIMESTAMP FORMAT A10
COLUMN CLONED_FROM_PDB_NAME FORMAT A15
 
SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME
  FROM CDB_PDB_HISTORY
  WHERE CON_ID > 2
  ORDER BY CON_ID;

Sample output:

DB_NAME    CON_ID PDB_NAME        OPERATION        OP_TIMESTA CLONED_FROM_PDB
---------- ------ --------------- ---------------- ---------- ---------------
NEWCDB          3 HRPDB           CREATE           10-APR-12  PDB$SEED
NEWCDB          4 SALESPDB        CREATE           17-APR-12  PDB$SEED
NEWCDB          5 TESTPDB         CLONE            30-APR-12  SALESPDB

Note:

When the current container is a PDB, the CDB_PDB_HISTORY view shows the history of the current PDB only. A local user whose current container is a PDB can query the DBA_PDB_HISTORY view and exclude the CON_ID column from the query to view the history of the current PDB.