For an application, a PDB is a self-contained, fully functional Oracle database. You can consolidate PDBs into a single CDB to achieve economies of scale, while maintaining isolation between PDBs.

You can use PDBs to achieve the following specific goals:

• Store data specific to an application

  For example, a sales application can have its own dedicated PDB, and a human resources application can have its own dedicated PDB. Alternatively, you can create an application container, which is a named collection of PDBs, to store an application back end containing common data and metadata (see "About Application Containers").

• Move data into a different CDB

  A database is "pluggable" because you can package it as a self-contained unit, called an unplugged PDB, and then move it into another CDB.

• Perform rapid upgrades

  You can unplug a PDB from CDB at a lower Oracle Database release, and then plug it in to a CDB at a higher release.

• Copy data quickly without loss of availability

  For testing and development, you can clone a PDB while it remains open, storing the clone in the same or a different CDB. Optionally, you can specify the PDB as a refreshable clone PDB. Alternatively, you use the Oracle-supplied seed PDB or a user-created application seed to copy new PDBs.

• Reference data in a different CDB

  You can create a proxy PDB that refers to a different PDB, either in the same CDB or in a separate CDB. When you issue statements in the proxy PDB, they execute in the referenced PDB.

• Isolate grants within PDBs

  A local or common user with appropriate privileges can grant EXECUTE privileges on a schema object to PUBLIC within an individual PDB.

Containers in a CDB share the same namespace, which means that they must have unique names within this namespace.

Names for the following containers must not conflict within the same CDB:

• The CDB root

• PDBs plugged in to the CDB root

• Application roots

• Application PDBs

For example, if the same CDB contains the application containers saas_sales_ac and saas_sales_test_ac, then two application PDBs that are both named cust1 cannot simultaneously reside in both containers. The namespace rules also prevent creation of a PDB named cust1pdb in the CDB root and a PDB named cust1pdb in an application root.

Names for PDBs and application root containers must follow the same rules as net service names. Moreover, because a PDB or application root has a service with its own name, the container name must be unique across all CDBs whose services are exposed through a specific listener. The first character of a user-created container name must be alphabetic, with remaining characters either alphanumeric or an underscore (_). Because service names are case-insensitive, container names are case-insensitive, and are in upper case even if specified using delimited identifiers.

By default, a user connected to one PDB must use database links to access objects in a different PDB. This behavior is directly analogous to a user in a non-CDB accessing objects in a different non-CDB.

Figure 2-1 Database Link Between PDBs

In this illustration, a PDB administrator is connected to the PDB named hrpdb1. By default, during this user session, c##dba cannot query the emp2 table in hrpdb2 without specifying a database link.

Description of "Figure 2-1 Database Link Between PDBs"

Exceptions to the rule include:

• A data-linked common object, which is accessible by all application PDBs that contain a data link that points to this object. For example, the application container saas_sales_ac might contain the data-linked table usa_zipcodes within its application. In this case, common CDB user c##dba can connect to an application PDB in this container, and then query usa_zipcodes even though the actual table resides in the application root. In this case, no database link is required.

• The CONTAINERS() clause in SQL issued from the CDB root or application root. Using this clause, you can query data across all PDBs plugged in to the root.

When creating a proxy PDB, you must specify a database link name in the FROM clause of the CREATE PLUGGABLE DATABASE ... AS PROXY statement. If the proxy PDB and referenced PDB reside in separate CDBs, then the database link must be defined in the root of the CDB that will contain the proxy PDB. The database link must connect either to the remote referenced PDB or to the CDB root of the remote CDB.

In a newly created non-CDB that does not yet contain user data, the data dictionary contains only system metadata. For example, the TAB$ table contains rows that describe only Oracle-supplied tables, for example, TRIGGER$ and SERVICE$.

The following graphic depicts three underlying data dictionary tables, with the red bars indicating rows describing the system.

Figure 2-2 Unmixed Data Dictionary Metadata in a Non-CDB

Description of "Figure 2-2 Unmixed Data Dictionary Metadata in a Non-CDB"

If users create their own schemas and tables in this non-CDB, then the data dictionary now contains some rows that describe Oracle-supplied entities, and other rows that describe user-created entities. For example, the TAB$ dictionary table now has a row describing employees and a row describing departments.

Figure 2-3 Mixed Data Dictionary Metadata in a Non-CDB

Description of "Figure 2-3 Mixed Data Dictionary Metadata in a Non-CDB"

In a CDB, the data dictionary metadata is split between the root and the PDBs. In the following figure, the employees and departments tables reside in a PDB. The data dictionary for this user data also resides in the PDB. Thus, the TAB$ table in the PDB has a row for the employees table and a row for the departments table.

Figure 2-4 Data Dictionary Architecture in a CDB

Description of "Figure 2-4 Data Dictionary Architecture in a CDB"

The preceding graphic shows that the data dictionary in the PDB contains pointers to the data dictionary in the root. Internally, Oracle-supplied objects such as data dictionary table definitions and PL/SQL packages are represented only in the root. This architecture achieves two main goals within the CDB:

• Reduction of duplication

  For example, instead of storing the source code for the DBMS_ADVISOR PL/SQL package in every PDB, the CDB stores it only in CDB$ROOT, which saves disk space.

• Ease of database upgrade

  If the definition of a data dictionary table existed in every PDB, and if the definition were to change in a new release, then each PDB would need to be upgraded separately to capture the change. Storing the table definition only once in the root eliminates this problem.

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 root. As depicted in Figure 2-4, the OBJ$ table in each PDB uses an internal mechanism called a metadata link to point to the definition of OBJ$ stored in the root.

  The data corresponding to a metadata link resides in its PDB, not in the 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. The data dictionary views in the PDB and in the 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. An application PDB uses an internal mechanism called a data link to refer to the objects 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. 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 object fetches rows only from the application root. However, when queried in an application PDB, an extended data-linked object fetches rows from both the application root and application PDB.

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

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 2-5 Dictionary Views in a CDB

Description of "Figure 2-5 Dictionary Views in a CDB"

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 PDB, the data dictionary appears as it would for a non-CDB. When the current container is the root, however, a common user can query CDB_ views to see metadata for the root and for PDBs for which this user is privileged.

The following table shows a scenario involving 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(*)
--------
      41

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(*)
----------
        45

The data dictionary that stores the metadata for the CDB as a whole is stored only in the system tablespaces.

The data dictionary that stores the metadata for a specific PDB is stored in the self-contained tablespaces dedicated to this PDB. The PDB tablespaces contain both the data and metadata for an application back end. Thus, each set of data dictionary tables is stored in its own dedicated set of tablespaces.

Within the context of either the system container (CDB) or an application container, a common user is a database user that has the same identity in the root and in every existing and future PDB within this container.

Every common user can connect to and perform operations within the root of its container, and within any PDB in which it has sufficient privileges. Some administrative tasks must be performed by a common user. Examples include creating a PDB and unplugging a PDB.

For example, SYSTEM is a CDB common user with DBA privileges. Thus, SYSTEM can connect to the CDB root and any PDB in the database. You might create a common user saas_sales_admin in the saas_sales application container. In this case, the saas_sales_admin user could only connect to the saas_sales application root or to an application PDB within the saas_sales application container.

Every common user is either Oracle-supplied or user-created. Examples of Oracle-supplied common users are SYS and SYSTEM. Every user-created common user is either a CDB common user, or an application common user.

Figure 2-7 shows sample users and schemas in two PDBs: hrpdb and salespdb. SYS and c##dba are CDB common users who have schemas in CDB$ROOT, hrpdb, and salespdb. Local users hr and rep exist in hrpdb. Local users hr and rep also exist in salespdb.

Figure 2-7 Users and Schemas in a CDB

Description of "Figure 2-7 Users and Schemas in a CDB"

Common users have the following characteristics:

• A common user can log in to any container (including CDB$ROOT) in which it has the CREATE SESSION privilege.

  A common user need not have the same privileges in every container. For example, the c##dba user may have the privilege to create a session in hrpdb and in the root, but not to create a session in salespdb. Because a common user with the appropriate privileges can switch between containers, a common user in the root can administer PDBs.

• An application common user does not have the CREATE SESSION privilege in any container outside its own application container.

  Thus, an application common user is restricted to its own application container. For example, the application common user created in the saas_sales application can connect only to the application root and the PDBs in the saas_sales application container.

• The names of user-created CDB common users must follow the naming rules for other database users. Additionally, the names must begin with the characters specified by the COMMON_USER_PREFIX initialization parameter, which are c## or C## by default. Oracle-supplied common user names and user-created application common user names do not have this restriction.

  No local user name may begin with the characters c## or C##.

• Every common user is uniquely named across all PDBs within the container (either the system container or a specific application container) in which it was created.

  A CDB common user is defined in the CDB root, but must be able to connect to every PDB with the same identity. An application common user resides in the application root, and may connect to every application PDB in its container with the same identity.

A local user is a database user that is not common and can operate only within a single PDB.

Local users have the following characteristics:

• A local user is specific to a PDB and may own a schema in this PDB.

  In Figure 2-7, local user hr on hrpdb owns the hr schema. On salespdb, local user rep owns the rep schema, and local user hr owns the hr schema.

• A local user can administer a PDB, including opening and closing it.

  A common user with SYSDBA privileges can grant SYSDBA privileges to a local user. In this case, the privileged user remains local.

• A local user in one PDB cannot log in to another PDB or to the CDB root.

  For example, when local user hr connects to hrpdb, hr cannot access objects in the sh schema that reside in the salespdb database without using a database link. In the same way, when local user sh connects to the salespdb PDB, sh cannot access objects in the hr schema that resides in hrpdb without using a database link.

• The name of a local user must not begin with the characters c## or C##.

• The name of a local user must only be unique within its PDB.

  The user name and the PDB in which that user schema is contained determine a unique local user. Figure 2-7 shows that a local user and schema named rep exist on hrpdb. A completely independent local user and schema named rep exist on the salespdb PDB.

The following table describes a scenario involving the CDB in Figure 2-7. Each row describes an action that occurs after the action in the preceding row. Common user SYSTEM creates local users in two PDBs.

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

SQL> CREATE USER rep IDENTIFIED BY password; 

User created.

SQL> GRANT CREATE SESSION TO rep;

Grant succeeded.

SQL> CONNECT rep@salespdb
Enter password: *******
ERROR:
ORA-01017: invalid username/password; logon denied

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

SQL> CREATE USER rep IDENTIFIED BY password; 

User created.

SQL> GRANT CREATE SESSION TO rep;

Grant succeeded.

SQL> CONNECT rep@salespdb
Enter password: *******
Connected.

A common role exists either in the CDB root or an application root, and applies to every PDB within the root container (either the CDB or the application container).

Common roles are useful for cross-container operations, ensuring that a common user has a role in every PDB. Every common role is one of the following types:

• Oracle-supplied

  All Oracle-supplied roles, such as DBA and PUBLIC, are common to the CDB.

• User-created

  Create a common role by executing CREATE ROLE ... CONTAINER=ALL in either the CDB root or application root, which determines the container to which the role is common. The standard naming conventions apply. Additionally, the names of CDB common roles must begin with the characters specified by the COMMON_USER_PREFIX initialization parameter, which are c## or C## by default.

The scope of the role is the scope of the root within which it is defined. If you define the role in CDB$ROOT, then its scope is the entire CDB. If you define the role within application root, then its scope is the application container.

A local role exists only in a single PDB, just as a role in a non-CDB exists only in the non-CDB.

A local role can only contain roles and privileges that apply within the container in which the role exists. For example, if you create the local role pdbadmin in hrpdb, then the scope of this role is restricted to this PDB.

PDBs in the same CDB, or in the same application container, may contain local roles with the same name. For example, the user-created role pdbadmin may exist in both hrpdb and salespdb. However, these roles are completely independent of each other, just as they would be in separate non-CDBs.

In a CDB, every act of granting, whether local or common, occurs within a container. The container may be the CDB root, an application root, or a PDB.

If the current container is the CDB root, then granting commonly means granting to all containers in the CDB. If the current container is an application root, however, then granting commonly means granting to all PDBs in the current application container.

The basic principles of granting are as follows:

• Both common and local phenomena may grant and be granted locally.

• Only common phenomena may grant or be granted commonly.

Local users, roles, and privileges are restricted to a particular PDB. Thus, local users may not grant roles and privileges commonly, and local roles and privileges may not be granted commonly.

The following sections describe the implications of the preceding principles.

Roles and privileges may be granted locally to users and roles regardless of whether the grantees, grantors, or roles being granted are local or common.

The following table explains the valid possibilities for locally granted roles and privileges.

^Footnote 1

Privileges in this role are available to the grantee only in the container in which the role was granted, regardless of whether the privileges were granted to the role locally or commonly.

^Footnote 2

Privileges in this role are available to the grantee only in the container in which the role was granted and created.

Privileges and common roles may be granted commonly.

User accounts or roles may be granted roles and privileges commonly only if the grantees and grantors are both common. If a role is being granted commonly, then the role itself must be common. The following table explains the possibilities for common grants.

^Footnote 3

Privileges that were granted commonly to a common role are available to the grantee across all containers. In addition, any privilege granted locally to a common role is available to the grantee only in the container in which that privilege was granted to the common role.

In a CDB, PUBLIC is a common role. In a PDB, privileges granted locally to PUBLIC enable all local and common user account to exercise these privileges in this PDB only.

Every privilege and role granted to Oracle-supplied users and roles is granted commonly except for system privileges granted to PUBLIC, which are granted locally. This exception exists because you may want to revoke some grants included by default in Oracle Database, such as EXECUTE on the SYS.UTL_FILE package.

Assume that local user account hr exists in hrpdb. This user locally grants the SELECT privilege on hr.employees to PUBLIC. Common and local users in hrpdb may exercise the privilege granted to PUBLIC. User accounts in salespdb or any other PDB do not have the privilege to query hr.employees in hrpdb.

Privileges granted commonly to PUBLIC enable all local users to exercise the granted privilege in their respective PDBs and enable all common users to exercise this privilege in the PDBs to which they have access. Oracle recommends that users do not commonly grant privileges and roles to PUBLIC.

In this scenario, SYSTEM creates common user c##dba and tries to give this user privileges to query a table in the hr schema in hrpdb.

The scenario shows how the CONTAINER clause affects grants of roles and privileges. The first column shows operations in CDB$ROOT. The second column shows operations in hrpdb.

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

SQL> CREATE USER c##dba 
IDENTIFIED BY password 
CONTAINER=ALL;

SQL> GRANT CREATE SESSION 
  TO c##dba;

SQL> CREATE ROLE c##admin 
  CONTAINER=ALL;

SQL> GRANT SELECT ANY TABLE 
  TO c##admin;
Grant succeeded.

SQL> GRANT c##admin TO c##dba;
SQL> EXIT;

SQL> CONNECT c##dba@hrpdb
Enter password: *******
ERROR: ORA-01045: user c##dba
lacks CREATE SESSION privilege;
logon denied

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

SQL> GRANT CONNECT, RESOURCE TO c##dba;
Grant succeeded.
SQL> EXIT

SQL> CONNECT c##dba@hrpdb
Enter password: *******
Connected.

SQL> SELECT COUNT(*)
FROM hr.employees;
select * from hr.employees
                 *
ERROR at line 1:
ORA-00942: table or view does
not exist

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

SQL> GRANT SELECT ANY TABLE
 TO c##admin CONTAINER=ALL;
Grant succeeded.

SQL> SELECT COUNT(*) FROM 
  hr.employees;
select * from hr.employees
                 *
ERROR at line 1:
ORA-00942: table or view does
not exist

SQL> GRANT c##admin TO c##dba
  CONTAINER=ALL;
Grant succeeded.

SQL> SELECT COUNT(*)
  FROM hr.employees;

  COUNT(*)
----------
       107