A container data object is a table or view containing data pertaining to multiple containers or the whole CDB.

Container data privileges support a general requirement in which multiple PDBs reside in a single CDB, but with different local administration requirements. For example, if application DBAs do not want to administer locally, then they can grant container data privileges on appropriate views to the common users. In this case, the CDB administrator can access the data for these PDBs. In contrast, PDB administrators who do not want the CDB administrator accessing their data do not grant container data privileges.

Examples of container data objects are Oracle-supplied views whose names begin with V$ and CDB_. All container data objects have a CON_ID column. The following table shows the meaning of the values for this column.

In a CDB, for every DBA_ view, a corresponding CDB_ view exists. The owner of a CDB_ view is the owner of the corresponding DBA_ view. The following graphic shows the relationship among the different categories of dictionary views:

Figure 8-3 Dictionary Views in a CDB

Description of "Figure 8-3 Dictionary Views in a CDB"

When the current container is a PDB, a user can view data dictionary information for the current PDB only. When the current container is the CDB root, however, a common user can query CDB_ views to see metadata for the CDB root and for PDBs for which this user is privileged.

The following table describes queries of CDB_ views. Each row describes an action that occurs after the action in the preceding row.

SQL> CONNECT SYSTEM
Enter password: ********
Connected.

SQL> SELECT COUNT(*) FROM CDB_USERS 
WHERE CON_ID=1;

COUNT(*)
--------
      38

SQL> SELECT COUNT(DISTINCT(CON_ID)) 
FROM CDB_USERS;
 
COUNT(DISTINCT(CON_ID))
-----------------------
                      4

SQL> CONNECT SYSTEM@hrdb
Enter password: ********
Connected.

SQL> SELECT COUNT(*) FROM CDB_USERS;
 
  COUNT(*)
----------
        48

SQL> SELECT COUNT(*) FROM DBA_USERS;
 
  COUNT(*)
----------
        48

Views with the prefix DBA_ show all relevant information in the entire database. DBA_ views are intended only for administrators.

The following sample query shows information about all objects in the database:

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM   DBA_OBJECTS
ORDER BY OWNER, OBJECT_NAME;

Views with the prefix ALL_ refer to the user's overall perspective of the database.

These views return information about schema objects to which the user has access through public or explicit grants of privileges and roles, in addition to schema objects that the user owns.

For example, the following query returns information about all the objects to which you have access:

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE 
FROM   ALL_OBJECTS
ORDER BY OWNER, OBJECT_NAME; 

Because the ALL_ views obey the current set of enabled roles, query results depend on which roles are enabled, as shown in the following example:

SQL> SET ROLE ALL;
 
Role set.
 
SQL> SELECT COUNT(*) FROM ALL_OBJECTS;
 
COUNT(*)
----------
68295
 
SQL> SET ROLE NONE;
 
Role set.
 
SQL> SELECT COUNT(*) FROM ALL_OBJECTS;
 
COUNT(*)
----------
53771

Application developers should be cognizant of the effect of roles when using ALL_ views in a stored procedure, where roles are not enabled by default.

The views most likely to be of interest to typical database users are those with the prefix USER_.

These views:

• Refer to the user's private environment in the database, including metadata about schema objects created by the user, grants made by the user, and so on

• Display only rows pertinent to the user, returning a subset of the information in the ALL_ views

• Has columns identical to the other views, except that the column OWNER is implied

• Can have abbreviated PUBLIC synonyms for convenience

For example, the following query returns all the objects contained in your schema:

SELECT OBJECT_NAME, OBJECT_TYPE 
FROM   USER_OBJECTS
ORDER BY OBJECT_NAME;

DUAL is a small table in the data dictionary that Oracle Database and user-written programs can reference to guarantee a known result.

The dual table is useful when a value must be returned only once, for example, the current date and time. All database users have access to DUAL.

The DUAL table has one column called DUMMY and one row containing the value X. The following example queries DUAL to perform an arithmetical operation:

SQL> SELECT ((3*4)+5)/3 FROM DUAL;
 
((3*4)+5)/3
-----------
 5.66666667

The CDB uses an internal linking mechanism to separate data dictionary information.

Specifically, Oracle Database uses the following automatically managed pointers:

• Metadata links

  Oracle Database stores metadata about dictionary objects only in the CDB root. For example, the column definitions for the OBJ$ dictionary table, which underlies the DBA_OBJECTS data dictionary view, exist only in the CDB root. As depicted in Figure 8-2, the OBJ$ table in each PDB uses an internal mechanism called a metadata link to point to the definition of OBJ$ stored in the CDB root.

  The data corresponding to a metadata link resides in its PDB, not in the CDB root. For example, if you create table mytable in hrpdb and add rows to it, then the rows are stored in the PDB data files, not in the CDB root data files.

  The data dictionary views in the PDB and in the CDB root contain different rows. For example, a new row describing mytable exists in the OBJ$ table in hrpdb, but not in the OBJ$ table in the CDB root. Thus, a query of DBA_OBJECTS in the CDB root and DBA_OBJECTS in hrdpb shows different results.

• Data links

  Note:

  Data links were called object links in Oracle Database 12c Release 1 (12.1.0.2).

  In some cases, Oracle Database stores the data (not only metadata) for an object only once in the application root of an application container. Consider an e-commerce company that has different PDBs for different regions. The application root might store a table named postal_codes, which lists all U.S. zip codes. Every application PDB in this container requires access to the common postal_codes table.

  An application PDB uses an internal mechanism called a data link to refer to the object in the application root. The application PDB in which the data link was created also stores the data link description. A data link inherits the data type of the object to which it refers.

• Extended data link

  An extended data link is a hybrid of a data link and a metadata link. Like a data link, an extended data link refers to an object in an application root. However, the extended data link also refers to a corresponding object in the application PDB. For example, an application PDB might have an extended data link table that stores both U.S. zip codes and Canadian postal codes. Like a metadata link, the object in the application PDB inherits metadata from the corresponding object in the application root.

  When queried in the application root, an extended data-linked table fetches rows only from the application root, for example, just U.S. zip codes. However, when queried in an application PDB, an extended data-linked table fetches rows from both the application root and application PDB, for example, U.S. zip codes and Canadian postal codes.

Oracle Database automatically creates and manages metadata and data links to CDB$ROOT. Users cannot add, modify, or remove these links.

Oracle Database creates public synonyms for many data dictionary views so users can access them conveniently.

The security administrator can also create additional public synonyms for schema objects that are used systemwide. Oracle recommends against using the same name for a private schema object and a public synonym.

Much of the data dictionary information is in the data dictionary cache because the database constantly requires the information to validate user access and verify the state of schema objects.

The caches typically contain the parsing information. The COMMENTS columns describing the tables and their columns are not cached in the dictionary cache, but may be cached in the database buffer cache.

Other Oracle Database products can reference existing views and create additional data dictionary tables or views of their own.

Oracle recommends that application developers who write programs referring to the data dictionary use the public synonyms rather than the underlying tables. Synonyms are less likely to change between releases.