Administering a CDB

Administering a multitenant container database (CDB) includes tasks such as accessing a container, modifying a CDB, executing DDL statements, and running Oracle-supplied SQL scripts.

Administering a CDB is similar to administering a non-CDB, with some differences.

Most differences occur because some administrative tasks apply to the entire CDB, whereas others apply to specific containers.

Current Container

The data dictionary in each container in a CDB is separate, and the current container is the container whose data dictionary is used for name resolution and for privilege authorization.

The current container can be the CDB root, an application root, a PDB, or an application PDB. Each session has exactly one current container at any point in time. However, a session can switch from one container to another.

Each container has a unique ID and name in a CDB. You can use the CON_ID and CON_NAME parameters in the USERENV namespace to determine the current container ID and name with the SYS_CONTEXT function. For example, the following query returns the current container name:

SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

You can access a container in various ways. For example, you can use the SQL*Plus CONNECT command, and you can use an ALTER SESSION SET CONTAINER statement to switch the container of the current session.

The following rules apply to the current container in a CDB:

• The current container can be CDB$ROOT (CDB root) only for common users.
• The current container can be a specific PDB for common users and local users.
• The current container can be an application root only for common users or for application common users created in the application root.
• The current container can be a specific application PDB for common users, application common users, and local users.
• The current container must be the CDB root or an application root when a SQL statement includes CONTAINER = ALL.

You can include the CONTAINER clause in several SQL statements, such as the CREATE USER, ALTER USER, CREATE ROLE, GRANT, REVOKE, and ALTER SYSTEM statements. Note the following rules about CONTAINER = ALL:

• When a SQL statement includes CONTAINER = ALL and the current container is the CDB root, the SQL statement affects all containers in the CDB, including all PDBs, application roots, and application PDBs.
  • When a SQL statement includes CONTAINER = ALL and the current container is an application root, the SQL statement affects all containers in the application container, including the application root and all the application PDBs that belong to the application root. The SQL statement does not affect the CDB root or any PDBs or application PDBs that do not belong to the current application root.
  • Only a common user or application common user with the commonly granted SET CONTAINER privilege can run a SQL statement that includes CONTAINER = ALL.

Administrative Tasks in a CDB

Common users perform administrative tasks for a CDB.

A common user has a single identity and can log in to the CDB root, any application root, PDB, or application PDB in which it has privileges. Some tasks, such as starting up a CDB instance, can be performed only by a common user.

Other administrative tasks are the same for a CDB and a non-CDB. The following table describes some of these tasks and provides pointers to the relevant documentation.

Administrative Tasks Common to CDBs and Non-CDBs

Using Manageability Features in a CDB

For each of Oracle Database’s manageability features in a CDB, it is important to understand the data location and the data visibility.

When feature data resides in the CDB root, the data is not included when a PDB is unplugged. When the data resides in a PDB, however, the data remains both when the PDB is unplugged and when it is plugged in.

Generally, in a CDB, a common user can view data for the CDB root and for multiple PDBs when the common user’s current container is the CDB root. A common user can view this data by querying container data objects. The specific data that is visible varies for the manageability features. A user whose current container is a PDB can view data for that PDB only.

The following table describes how the manageability features work in a CDB.

Manageability Features in a CDB

To run SPA or SQL Tuning Advisor for SQL statements from a PDB, a common user must have the following privileges:

• Common SET CONTAINER privilege or local SET CONTAINER privilege in the PDB
• The privileges required to execute the SQL statements in the PDB

To connect to the root using the SQL*Plus CONNECT command:

1. Configure your environment so that you can open SQL*Plus.
2. Start SQL*Plus with the /NOLOG argument:

sqlplus /nolog

• Issue a SQL*Plus CONNECT command to connect to the root, as shown in the following examples.

Connecting to the Root with a Local Connection

This example connects to the root in the local CDB as user SYSTEM. SQL*Plus prompts for the SYSTEM user password.

connect system

Connecting to the Root with Operating System Authentication

This example connects locally to the root with the SYSDBA administrative privilege with operating system authentication.

connect / as sysdba

Connecting to the Root with a Net Service Name

Assume that clients are configured to have a net service name for the root in the CDB. For example, the net service name can be part of an entry in a tnsnames.ora file.

This example connects as common user c##dba to the database service designated by the net service name mycdb. SQL*Plus prompts for the c##dba user password.

connect c##[email protected]

Connecting to a PDB Using the SQL*Plus CONNECT Command

To connect to a PDB with the SQL*Plus CONNECT command, you can use easy connect or a net service name.

To connect to a PDB, a user must be one of the following:

• A common user with a CREATE SESSION privilege granted commonly or granted locally in the PDB
• A local user defined in the PDB with CREATE SESSION privilege

Only a user with SYSDBA, SYSOPER, SYSBACKUP, or SYSDG privilege can connect to a PDB that is in mounted mode.

To connect to a PDB using the SQL*Plus CONNECT command:

1. Configure your environment so that you can open SQL*Plus.
2. Start SQL*Plus with the /NOLOG argument:

sqlplus /nolog

• Issue a SQL*Plus CONNECT command using easy connect or a net service name to connect to the PDB.

Connecting to a PDB

Assume that clients are configured to have a net service name for each PDB that matches each PDB name. For example, the net service name can be part of an entry in a tnsnames.ora file.

The following command connects to the sh local user in the salespdb PDB:

CONNECT [email protected]

The following command connects to the SYSTEM common user in the salespdb PDB:

CONNECT [email protected]

Switching to a Container Using the ALTER SESSION Statement

When you are connected to a container as a common user, you can switch to a different container and application service using the ALTER SESSION statement.

You can use the following statement to switch to a different container and application service:

ALTER SESSION SET CONTAINER = container_name [SERVICE = service_name]

For container_name, specify one of the following:

• CDB$ROOT to switch to the CDB root
• PDB$SEED to switch to the PDB seed
• A PDB name to switch to the PDB

When the current container is the root, you can view the names of the PDBs in a CDB by querying the DBA_PDBS view.

For service_name, specify a service that is running in the PDB. You can list the services running in the containers of a CDB, excluding the CDB root, by issuing the following query with the CDB root as the current container:

COL NAME FORMAT A30

COL CON_NAME FORMAT A20

SELECT NAME,CON_NAME, CON_ID

  FROM V$ACTIVE_SERVICES

  WHERE UPPER(NAME) != CON_NAME

  AND CON_ID !=1

  ORDER BY CON_ID;

By default, when you switch to a container, the session uses the default service for the container. However, the default PDB service does not support all service attributes and features such as service metrics, Fast Application Notification (FAN), load balancing, Resource Manager, Transaction Guard, Application Continuity, and so on. It is best practice to use a nondefault service for the container by specifying SERVICE = service_name, where service_name is the name of the service.

With this new capability, connection pools can switch the service, and, when needed the PDB, on a connection when a connection is borrowed from the pool. Starting with Oracle Database 12c Release 2 (12.2.0.1), connection pools support more than one database service with universal connection pools (UCPs). It can also be used standalone.

When switching to a service, applications can consolidate to a CDB, while keeping the database services identified, prioritized, measured, and highly available. Switching to a nondefault service provides the following benefits:

• It preserves the service attributes and features.
• It eliminates too many connection pools with too many connections serving these tenants.
• It allows applications to use more database services for workload control without consuming too many connection pools. Customers can identify and prioritize workloads using services without over sizing the database connections.

To switch to a container using the ALTER SESSION statement:

1. In SQL*Plus, connect to a container as a common user with the required privileges.
2. Check the current open mode of the container to which you are switching.

To check the current open mode of the root or a PDB, query the OPEN_MODE column in the V$CONTAINERS view when the current container is the root.

The open mode of the root imposes limitations on the open mode of PDBs. For example, the root must be open before any PDBs can be open. Therefore, you might need to change the open mode of the root before changing the open mode of a PDB.

• If you are switching to a specific service, then ensure that the service is running.

To check the active status of the service, query the V$ACTIVE_SERVICES view when the current container is the CDB root.

If the service is not running, then use the SRVCTL utility or the DBMS_SERVICE package to start the service.

• Run the ALTER SESSION SET CONTAINER statement and specify the container to which you want to switch.

Include the SERVICE clause to switch to a specific application service.

The following examples switch to various containers using ALTER SESSION.

Switching to the PDB salespdb and Using the salesrep Service

ALTER SESSION SET CONTAINER = salespdb SERVICE = salesrep;

Switching to the PDB salespdb and Using the Default Service

ALTER SESSION SET CONTAINER = salespdb;

Switching to the CDB Root

ALTER SESSION SET CONTAINER = CDB$ROOT;

Switching to the PDB Seed

ALTER SESSION SET CONTAINER = PDB$SEED;

Switching Services Using a Dummy Service in the CDB Root

To design connection pooling that switches the container and the service, one method is to create a dummy service in the CDB root and set all required service attributes on this dummy service (for example, drain_timeout, TAF or Application Continuity). The service attributes must match across the CDB root and the PDB. To use this method, complete the following steps:

1. Connect to the dummy service when first creating the connection pool and when creating new connections.
2. As services are added to each PDB, set the same attributes on these real services.
3. When an application requires a connection, complete one of the following actions:
   1. Create a new connection to the dummy service, and switch to the PDB and service.
   1. Borrow a free connection in the pool and switch to the PDB and service.

You do not need to return to the CDB root when switching across PDBs.

You do not need to return to the CDB root when switching across PDBs.

Modifying a CDB at the System Level

You can set initialization parameters at the CDB level. In some cases, you can override these parameters at the PDB level.

The ALTER SYSTEM SET statement dynamically sets an initialization parameter in one or more containers.

A CDB uses an inheritance model for initialization parameters in which PDBs inherit initialization parameter values from the root. In this case, inheritance means that the value of a specific parameter in the root applies to a specific PDB.

A PDB can override the root setting for some parameters. In such cases, a PDB has an inheritance property for each initialization parameter that is either true or false. The inheritance property is true for a parameter when the PDB inherits the root’s value for the parameter; otherwise, the property is false.

The inheritance property for some parameters must be true. For other parameters, when the current container is the PDB, you can change the inheritance property by running the ALTER SYSTEM SET statement. If V$SYSTEM_PARAMETER.ISPDB_MODIFIABLE is TRUE for an initialization parameter, then the inheritance property can be false for the parameter.

When the current container is the root, the CONTAINER clause of the ALTER SYSTEM SET statement controls which PDBs inherit the parameter value being set. The CONTAINER clause has the following syntax:

CONTAINER = { CURRENT | ALL }

The following settings are possible:

• CURRENT

The parameter setting applies only to the current container. This is the default setting for CONTAINER. When the current container is the root, the parameter setting applies to the root and to any PDB with an inheritance property of true for the parameter.

• ALL

The parameter setting applies to all containers in the CDB, including the root and all PDBs. Specifying ALL sets the inheritance property to true for the parameter in all PDBs.

Modifying a CDB with ALTER SYSTEM

To modify a CDB at the system level, use the ALTER SYSTEM statement.

To use ALTER SYSTEM SET in the root in a CDB:

1. In SQL*Plus, ensure that the current container is the root.
2. Run the ALTER SYSTEM SET statement.

ALTER SYSTEM RESET OPEN_CURSORS SCOPE = SPFILE;

Setting an Initialization Parameter for All Containers

This ALTER SYSTEM SET statement sets the OPEN_CURSORS initialization parameter to 200 for the all containers and sets the inheritance property to TRUE in each PDB.

ALTER SYSTEM SET OPEN_CURSORS = 200 CONTAINER = ALL;

Setting an Initialization Parameter for the Root

This ALTER SYSTEM SET statement sets the OPEN_CURSORS initialization parameter to 200 for the root and for PDBs with an inheritance property of true for the parameter.

ALTER SYSTEM SET OPEN_CURSORS = 200 CONTAINER = CURRENT;

Modifying Containers When Connected to the CDB Root

You can modify the entire CDB or the root with the ALTER DATABASE statement.

The ALTER DATABASE statement modifies a CDB. When you are connected to the CDB root, the ALTER PLUGGABLE DATABASE statement can modify the open mode of one or more PDBs.

The behavior of ALTER DATABASE and ALTER PLUGGABLE DATABASE depends on which container you are connected to when you use the statement:

• Connected as a common user to CDB root

In this case, the ALTER DATABASE statement works the same as in a non-CDB. When an ALTER DATABASE statement with the RENAME GLOBAL_NAME clause modifies the domain of a CDB, it affects the domain of each PDB with a domain that defaults to that of the CDB. The ALTER PLUGGABLE DATABASE statement with the pdb_change_state clause modifies the open mode of one or more PDBs.

• Connected to a PDB

In this case, the ALTER DATABASE and ALTER PLUGGABLE DATABASE statements modify the current PDB only.

The following table lists which containers are modified by clauses in ALTER DATABASE and ALTER PLUGGABLE DATABASE statements.

Statements That Modify Containers in a CDB

Modifying an Entire CDB Using ALTER DATABASE

You can use the ALTER DATABASE statement to modify an entire CDB, including the root and all PDBs. Most ALTER DATABASE statements modify the entire CDB.

Prerequisites

To modify an entire CDB, the following prerequisites must be met:

• The current user must be a common user with the ALTER DATABASE privilege.
• To use an ALTER DATABASE statement with a recovery_clause, the current user must have the SYSDBA administrative privilege commonly granted. In this case, you must exercise this privilege using AS SYSDBA at connect time.

To modify an entire CDB:

1. In SQL*Plus, ensure that the current container is the root.
2. Use an ALTER DATABASE statement with a clause that modifies an entire CDB.

Backing Up the Control File for a CDB

The following ALTER DATABASE statement uses a recovery_clause to back up a control file.

ALTER DATABASE BACKUP CONTROLFILE TO '+DATA/dbs/backup/control.bkp';

Adding a Redo Log File to a CDB

The following ALTER DATABASE statement uses a logfile_clause to add redo log files.

ALTER DATABASE cdb ADD LOGFILE

  GROUP 4 ('/u01/logs/orcl/redo04a.log','/u02/logs/orcl/redo04b.log')

  SIZE 100M BLOCKSIZE 512 REUSE;

Setting the Undo Mode in a CDB Using ALTER DATABASE

When local undo is enabled, each container has its own undo tablespace for every instance in which it is open. When local undo is disabled, there is one undo tablespace for the entire CDB.

CDB Undo Mode

You can configure a CDB to use local undo in every container or to use shared undo (default) for the entire CDB.

A CDB runs either in local or shared undo mode. The undo mode applies to the entire CDB. Therefore, every container either uses shared undo or local undo.

You can specify the undo mode of a CDB during CDB creation in the ENABLE PLUGGABLE DATABASE clause of the CREATE DATABASE statement. If you do not specify the UNDO clause, then shared undo mode is the default. You can change the undo mode of a CDB after it is created by issuing an ALTER DATABASE statement and restarting the CDB.

To determine the current CDB undo mode, run the following query in the CDB root:

SELECT PROPERTY_NAME, PROPERTY_VALUE

FROM   DATABASE_PROPERTIES

WHERE  PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';

If the query returns TRUE for the PROPERTY_VALUE, then the CDB is in local undo mode. Otherwise, the CDB is in shared undo mode.

Local Undo Mode

Local undo mode means that each container has its own undo tablespace for every instance in which it is open.

In this mode, Oracle Database automatically creates an undo tablespace for every container in the CDB. For an Oracle RAC CDB, there is one active undo tablespace for each instance for each PDB in local undo mode.

Local undo mode provides increased isolation for each container and improves the efficiency of some operations, such as unplugging the container or performing point-in-time recovery on the container. In addition, local undo mode is required for some operations to be supported, such as relocating a PDB or cloning a PDB that is in open read/write mode.

When a CDB is in local undo mode, the following applies:

• Any user who has the appropriate privileges for the current container can create an undo tablespace for the container.
• Undo tablespaces are visible in static data dictionary views and dynamic performance (V$) views in every container in the CDB.

Shared Undo Mode

Shared undo mode means that only one active undo tablespace exists for a single-instance CDB. For an Oracle RAC CDB, there is one active undo tablespace for each instance.

When a CDB is in shared undo mode, the following applies:

• Only a common user who has the appropriate privileges and whose current container is the CDB root can create an undo tablespace.
• When the current container is not the CDB root, an attempt to create an undo tablespace fails and returns an error.
• Undo tablespaces are visible in static data dictionary views and dynamic performance (V$) views when the current container is the CDB root. Undo tablespaces are visible only in dynamic performance views when the current container is a PDB, an application root, or an application PDB.

Configuring a CDB to Use Local Undo Mode

You can change a CDB to local undo mode by issuing an ALTER DATABASE LOCAL UNDO ON statement and restarting the database.

When a CDB is in local undo mode, each container has its own undo tablespace for every instance in which it is open. Oracle Database automatically creates an undo tablespace in any container in the CDB that does not have one. If a PDB without an undo tablespace is cloned, relocated, or plugged into a CDB that is configured to use local undo mode, then Oracle Database automatically creates an undo tablespace for the PDB the first time it is opened.

When a CDB is changed from shared undo mode to local undo mode, Oracle Database creates the required undo tablespaces automatically.

1. If the CDB instance is open, then shut it down.
2. Start up the CDB instance in OPEN UPGRADE mode. For example:

STARTUP UPGRADE

3. In SQL*Plus, ensure that the current container is the CDB root. For example, enter the following:

SHOW CON_NAME

CON_NAME

------------------------------

CDB$ROOT

4. Query the current undo mode of the CDB:

SELECT PROPERTY_NAME, PROPERTY_VALUE 

FROM   DATABASE_PROPERTIES 

WHERE  PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';

5. To enable local undo, issue the following SQL statement:

ALTER DATABASE LOCAL UNDO ON;

6. Shut down and restart the CDB instance.
7. Optional: Manually create an undo tablespace in the PDB seed.

While Oracle Database creates an undo tablespace in the PDB seed automatically in local undo mode, you might want to control the size and configuration of the undo tablespace by creating an undo tablespace manually. To ensure the PDBs created from the PDB seed use the manually-created undo tablespace and not the automatically-created undo tablespace, you must set the UNDO_TABLESPACE initialization parameter to the manually-created undo tablespace, or drop the automatically-created undo tablespace.

7. In SQL*Plus, ensure that the current container is the root.
8. Place the PDB seed in open read/write mode:

ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE FORCE;

7. Switch container to the PDB seed:

ALTER SESSION SET CONTAINER=PDB$SEED;

7. Create an undo tablespace in the PDB seed. For example:

CREATE UNDO TABLESPACE seedundots1

   DATAFILE 'seedundotbs_1a.dbf'

   SIZE 10M AUTOEXTEND ON

RETENTION GUARANTEE;

7. Switch container to the root:

ALTER SESSION SET CONTAINER=CDB$ROOT;

7. Place the PDB seed in open read-only mode:

ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY FORCE;

Configuring a CDB to Use Shared Undo Mode

To change a CDB to use shared undo mode, use an ALTER DATABASE LOCAL UNDO OFF statement.

1. If the CDB instance is open, then shut it down.
2. Start up the CDB instance in OPEN UPGRADE mode. For example:

STARTUP UPGRADE

3. In SQL*Plus, ensure that the current container is the CDB root. For example, enter the following:

SHOW CON_NAME

CON_NAME

------------------------------

CDB$ROOT

4. Optionally, query the current undo mode of the CDB:

SELECT PROPERTY_NAME, PROPERTY_VALUE 

FROM   DATABASE_PROPERTIES 

WHERE  PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';

5. To turn off local undo, issue the following SQL statement:

ALTER DATABASE LOCAL UNDO OFF;

6. Shut down and restart the CDB instance.

When in shared undo mode, the CDB ignores any local undo tablespaces that were created when it was in local undo mode. Oracle recommends that you delete the unused local undo tablespaces.

Modifying the CDB Root Using ALTER DATABASE

To modify only the root of a CDB, use the ALTER DATABASE statement.

When the current container is the root, some ALTER DATABASE statements modify the root without directly modifying any of the PDBs.

Some statements set the defaults for the PDBs in the CDB. You can overwrite these defaults for a PDB by using the ALTER PLUGGABLE DATABASE statement.

Prerequisites

To modify the root, the current user must have the ALTER DATABASE privilege in the root.

To modify the root:

1. In SQL*Plus, ensure that the current container is the root.
2. Run an ALTER DATABASE statement with a clause that modifies the root.

The following examples modify the root.

A user whose current container is the root that is not explicitly assigned a tablespace uses the default permanent tablespace for the root. The tablespace specified in the ALTER DATABASE statement must exist in the root.

After executing this statement, the default type of subsequently created tablespaces in the root is bigfile. This setting is also the default for PDBs.

The tablespace or tablespace group specified in the ALTER DATABASE statement must exist in the root.

Changing the Default Permanent Tablespace for the Root

This ALTER DATABASE statement uses a DEFAULT TABLESPACE clause to set the default permanent tablespace to root_tbs for the root.

ALTER DATABASE DEFAULT TABLESPACE root_tbs;

Bringing a Data File Online for the Root

This ALTER DATABASE statement uses a database_file_clause to bring the /u02/oracle/cdb_01.dbf data file online.

ALTER DATABASE DATAFILE '/u02/oracle/cdb_01.dbf' ONLINE;

Changing the Default Tablespace Type for the Root

This ALTER DATABASE statement uses a SET DEFAULT TABLESPACE clause to change the default tablespace type to bigfile for the root.

ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;

Changing the Default Temporary Tablespace for the Root

This ALTER DATABASE statement uses a DEFAULT TEMPORARY TABLESPACE clause to set the default temporary tablespace to root_temp for the root.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE root_temp;

Executing SQL in a Different Container

To execute SQL in a different container, use the CONTAINERS clause for DML or the CONTAINER clause for DDL.

Issuing DML Statements on a Container in a CDB

A DML (data manipulation language) statement issued in a CDB or application root can modify a different container in the CDB. In addition, you can specify a default container target for DML statements.

DML statements can affect database objects in a specified container in a CDB.

The container is specified by container ID. Because the container ID can appear in more than one location, the database uses the following order of precedence:

1. The CON_ID specified in the WHERE clause of a DML statement
2. The CONTAINERS_DEFAULT_TARGET database property
3. The current container, which is either the CDB root or application root

In a CDB root or an application root, a DML statement that includes the CONTAINERS clause can modify a table or view in a single container in the CDB or application container. To use the CONTAINERS clause, specify the table or view being modified in the CONTAINERS clause and the container ID affected in the WHERE clause.

You can specify a target container in an INSERT VALUES statement by specifying a value for CON_ID in the VALUES clause. Also, you can specify a target container in an UPDATE or DELETE statement by specifying a CON_ID predicate in the WHERE clause. For example, the following DML statement updates the sales.customers table in the container with a CON_ID of 7:

UPDATE CONTAINERS(sales.customers) ctab 

   SET ctab.city_name='MIAMI' 

   WHERE ctab.CON_ID=7 

   AND CUSTOMER_ID=3425;

Specifying the Default Container for DML Statements in a CDB

To specify the default container for DML statements in a CDB, issue the ALTER DATABASE statement with the CONTAINERS DEFAULT TARGET clause.

When a DML statement is issued in a CDB root without specifying containers in the WHERE clause, the DML statement affects the default container for the CDB. The default container can be any container in the CDB, including the CDB root, a PDB, an application root, or an application PDB. Only one default container is allowed.

The CONTAINERS_DEFAULT_TARGET database property sets the default container. By default, this property is not set. You can determine the default target containers for a CDB by running the following query:

SELECT PROPERTY_VALUE 

FROM   DATABASE_PROPERTIES 

WHERE  PROPERTY_NAME='CONTAINERS_DEFAULT_TARGET';

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

The current user must have the commonly granted ALTER DATABASE privilege.

2. Run the ALTER DATABASE statement with the CONTAINERS DEFAULT TARGET clause.

Specifying the Default Container for DML Statements in a CDB

This example specifies that PDB1 is the default container for DML statements in the CDB.

ALTER DATABASE CONTAINERS DEFAULT TARGET = (PDB1);

Clearing the Default Container

This example clears the default container setting. When it is not set, the default container is the CDB root.

ALTER DATABASE CONTAINERS DEFAULT TARGET = NONE;

Executing DDL Statements in a CDB

In a CDB, you can execute a data definition language (DDL) statement in the current container or in all containers.

In a CDB, some DDL statements can apply to all containers or to the current container only.

To specify which containers are affected, use the CONTAINER clause:

CONTAINER = { CURRENT | ALL }

The following settings are possible:

• CURRENT means that the statement applies only to the current container.
• ALL means that the statement applies to all containers in the CDB, including the root and all PDBs.

The following restrictions apply to the CONTAINER clause in DDL statements:

• You can use the CONTAINER clause only with the DDL statements.

Statements and the CONTAINER Clause in a CDB

All other DDL statements apply to the current container only.

In addition to the usual rules for user, role, and profile names, the following rules and best practices apply when you create a user, role, or profile in a CDB:

• It is best practice for common user, role, and profile names to start with a prefix to avoid naming conflicts between common users, roles, and profiles and local users, roles, and profiles. You specify this prefix with the COMMON_USER_PREFIX initialization parameter in the CDB root. By default, the prefix is C## or c## in the CDB root.
• In an application container, it is best practice for application common user, role, and profile names to start with a prefix to avoid naming conflicts between application common users, roles, and profiles and local users, roles, and profiles. You specify this prefix with the COMMON_USER_PREFIX initialization parameter in the application root. By default, the prefix is NULL in an application root.
• When the COMMON_USER_PREFIX initialization parameter is set in an application root, the setting applies to the application common user, role, and profile names in the application container. The prefix can be different in the CDB root and in an application root, and the prefix can be different in different application containers.
• Common user, role, and profile names must consist only of ASCII characters. This restriction does not apply to application common user, role, and profile names.
• Local user, role, and profile names cannot start with the prefix specified for common users with the COMMON_USER_PREFIX initialization parameter.
• Local user, role, and profile names cannot start with C## or c##.
• Regardless of the value of COMMON_USER_PREFIX in the CDB root, application common user, role, and profile names cannot start with C## or c##.
• Application common user, role, and profile names cannot start with the prefix specified for common users with the COMMON_USER_PREFIX initialization parameter.

Executing a DDL Statement in the Current Container

Specify CURRENT in the CONTAINER clause of a DDL statement to execute the statement in the current container.

The current user must be granted the required privileges to execute the DDL statement in the current container. For example, to create a user, the current user must be granted the CREATE USER system privilege in the current container.

To execute a DDL statement in the current container:

1. In SQL*Plus, access a container.
2. Execute the DDL statement with CONTAINER set to CURRENT.

A local user’s user name cannot start with the prefix specified by the COMMON_USER_PREFIX initialization parameter. By default, in the CDB root, the prefix is C## or c##. An application root can specify its own prefix for an application container. In addition, a common user’s name must consist only of ASCII characters. The specified tablespace must exist in the PDB.

Creating Local User in a PDB

This example creates the local user testpdb in the current PDB.

CREATE USER testpdb IDENTIFIED BY password 

   DEFAULT TABLESPACE pdb1_tbs

   QUOTA UNLIMITED ON pdb1_tbs

   CONTAINER = CURRENT;

Executing a DDL Statement in All Containers in a CDB

Specify ALL in the CONTAINER clause of a DDL statement to execute the statement in all containers in a CDB.

To execute a DDL statement in all containers in a CDB:

1. In SQL*Plus, ensure that the current container is the root.
2. Execute the DDL statement with CONTAINER set to ALL.

A common user’s user name must start with the prefix specified by the COMMON_USER_PREFIX initialization parameter. By default, in the CDB root, the prefix is C## or c##. An application root can specify its own prefix for an application container. In addition, a common user’s name must consist only of ASCII characters. The specified tablespace must exist in the root and in all PDBs.

Creating Common User in a CDB

This example creates the common user c##testcdb.

CREATE USER c##testcdb IDENTIFIED BY password 

   DEFAULT TABLESPACE cdb_tbs

   QUOTA UNLIMITED ON cdb_tbs

   CONTAINER = ALL;

Running Oracle-Supplied SQL Scripts in a CDB

You can use the catcon.pl script to run Oracle-supplied SQL or SQL scripts within a CDB. You can run the script against any specified containers.

In a CDB, the catcon.pl script is the best way to run SQL scripts and SQL statements.

An Oracle Database installation includes several SQL scripts. These scripts perform operations such as creating data dictionary views and installing options.

The catcon.pl script can run scripts in the root and in specified PDBs in the correct order, and it generates log files that you can view to confirm that the SQL script or SQL statement did not generate unexpected errors. It also starts multiple processes and assigns new scripts to them as they finish running scripts previously assigned to them.

Syntax and Parameters for catcon.pl

The catcon.pl script is a Perl script that must be run at an operating system prompt.

The catcon.pl script has the following syntax and parameters:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl  

[--usr username[/password]] 

[--int_usr username[/password]] 

[--script_dir directory] 

[--log_dir directory] 

[{--incl_con|--excl_con} container] 

[--echo] 

[--spool] 

[--error_logging { ON | errorlogging-table-other-than-SPERRORLOG } ] 

[--app_con application_root] 

[--no_set_errlog_ident] 

[--diag] 

[-ignore_unavailable_pdbs] 

[--verbose] 

[--force_pdb_mode pdb_mode] 

[--recover] 

--log_file_base log_file_name_base 

-- { SQL_script [arguments] | --x'SQL_statement' }

Ensure that --x SQL_statement is preceded by -- if it follows any single-letter parameter. If --x SQL_statement is preceded by a script name or another --x SQL_statement, then do not precede it with --. Also, note that the SQL statement must be inside single quotation marks.

Command line parameters to SQL scripts can be introduced using --p. Interactive (or secret) parameters to SQL scripts can be introduced using --P.

To view the help for the catcon.pl script, change directories to $ORACLE_HOME/perl/bin/, and then run the following command:

perl $ORACLE_HOME/rdbms/admin/catcon.pl --help

The following table describes the catcon.pl parameters. A parameter is optional unless it is indicated that it is required.

The short parameter names in the following table are for backward compatibility. Some parameters do not have short names.

catcon.pl Parameters

Running the catcon.pl Script

Examples illustrate running the catcon.pl script.

If a SQL script or SQL statement run by catcon.pl performs data manipulation language (DML) or data definition language (DDL) operations, then the containers being modified must be in read/write mode.

To run the catcon.pl script:

1. Open a command line prompt.
2. Run the catcon.pl script and specify one or more SQL scripts or SQL statements:

cd $ORACLE_HOME/perl/bin/

perl $ORACLE_HOME/rdbms/admin/catcon.pl parameters SQL_script

perl $ORACLE_HOME/rdbms/admin/catcon.pl parameters -- --xSQL_statement

Running the catblock.sql Script in All Containers in a CDB

The following example runs the catblock.sql script in all of the containers of a CDB (the backslash indicates line continuation):

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \

--usr SYS --script_dir $ORACLE_HOME/rdbms/admin \

--log_file_base catblock_output catblock.sql

The following parameters are specified:

• The --usr parameter specifies that SYS user runs the script in each container.
• The --script_dir parameter specifies that the SQL script is in the $ORACLE_HOME/rdbms/admin directory.
• The --log_file_base parameter specifies that the base name for log file names is catblock_output.

Default parameter values are used for all other parameters. Neither the --incl_con nor the --excl_con parameter is specified. Therefore, catcon.pl runs the script in all containers by default.

Running the catblock.sql Script in Specific PDBs

The following example runs the catblock.sql script in the hrpdb and salespdb PDBs in a CDB.

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \

--usr SYS --int_usr SYS --script_dir $ORACLE_HOME/rdbms/admin \

--log_dir '/disk1/script_output' --incl_con 'HRPDB SALESPDB' \

--log_file_base catblock_output catblock.sql

The following parameters are specified:

• The --usr parameter specifies that SYS user runs the script in each container.
• The --int_usr parameter specifies that SYS user performs internal tasks.
• The --script_dir parameter specifies that the SQL script is in the $ORACLE_HOME/rdbms/admin directory.
• The --log_dir parameter specifies that the output files are placed in the /disk1/script_output directory.
• The --incl_con parameter specifies that the SQL script is run in the hrpdb and salespdb PDBs. The script is not run in any other containers in the CDB.
• The --log_file_base parameter specifies that the base name for log file names is catblock_output.

Running the catblock.sql Script in All Containers Except for Specific PDBs

The following example runs the catblock.sql script in all of the containers in a CDB except for the hrpdb and salespdb PDBs.

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \

--usr SYS --script_dir $ORACLE_HOME/rdbms/admin \

--log_dir '/disk1/script_output' --excl_con 'HRPDB SALESPDB' \ 

--log_file_base catblock_output catblock.sql

The following parameters are specified:

• The --usr parameter specifies that SYS user runs the script in each container.
• The --script_dir parameter specifies that the SQL script is in the $ORACLE_HOME/rdbms/admin directory.
• The --log_dir parameter specifies that the output files are placed in the /disk1/script_output directory.
• The --excl_con parameter specifies that the SQL script is run in all of the containers in the CDB except for the hrpdb and salespdb PDBs.
• The --log_file_base parameter specifies that the base name for log file names is catblock_output.

Running a SQL Script with Command Line Parameters

The following example runs the custom_script.sql script in all of the containers of a CDB.

cd $ORACLE_HOME/perl/bin/

perl $ORACLE_HOME/rdbms/admin/catcon.pl --usr SYS --script_dir /u01/scripts \

--log_file_base custom_script_output custom_script.sql '--phr' \

'--PEnter password for user hr:'

The following parameters are specified:

• The --usr parameter specifies that SYS user runs the script in each container.
• The --script_dir parameter specifies that the SQL script is in the /u01/scripts directory.
• The --log_file_base parameter specifies that the base name for log file names is custom_script_output.
• The --p parameter specifies hr for a command line parameter
• The --P parameter specifies an interactive parameter that prompts for the password of user hr.

Default parameter values are used for all other parameters. Neither the -incl_con nor the -excl_con parameter is specified. Therefore, catcon.pl runs the script in all containers by default.

Running a SQL Statement in All Containers in a CDB

The following example runs a SQL statement in all of the containers of a CDB.

cd $ORACLE_HOME/perl/bin/

perl $ORACLE_HOME/rdbms/admin/catcon.pl --usr SYS --echo \

--log_file_base select_output -- --x"SELECT * FROM DUAL"

The following parameters are specified:

• The --usr parameter specifies that SYS user runs the script in each container.
• The --echo parameter shows output for the SQL statement.
• The --log_file_base parameter specifies that the base name for log file names is select_output.
• The SQL statement SELECT * FROM DUAL is inside quotation marks and is preceded by --x. Because --x is preceded by a parameter (--log_file_base), it must be preceded by --.

Default parameter values are used for all other parameters. Neither the -incl_con nor the -excl_con parameter is specified. Therefore, catcon.pl runs the SQL statement in all containers by default.

Executing Code in Containers Using the DBMS_SQL Package

When you are executing PL/SQL code in a container in a CDB, and you want to execute one or more SQL statements in a different container, use the DBMS_SQL package to switch containers.

For example, you can use the DBMS_SQL package to switch containers when you need to perform identical actions in more than one container.

The following are considerations for using DBMS_SQL to switch containers:

• A transaction cannot span multiple containers.

If the set of actions you must perform in the target container requires a transaction, then consider using an autonomous transaction and perform a commit or rollback as the last action.

• SET ROLE statements are not allowed.

Performing Identical Actions in More Than One Container

This example includes a PL/SQL block that creates the identact table in the hr schema in two PDBs (pdb1 and pdb2). The example also inserts a row into the identact table in both PDBs.

DECLARE

  c1 INTEGER;

  rowcount INTEGER;

  taskList VARCHAR2(32767) :=

    'DECLARE

      PRAGMA AUTONOMOUS TRANSACTION;

     BEGIN

       -- Create the hr.identact table.

       EXECUTE IMMEDIATE

         ''CREATE TABLE hr.identact

             (actionno NUMBER(4) NOT NULL,

              action VARCHAR2 (10))'';

       EXECUTE IMMEDIATE

         ''INSERT INTO identact VALUES(1, 'ACTION1')'';

       -- A commit is required if the tasks include DML.

       COMMIT;

       EXCEPTION

         WHEN OTHERS THEN

           -- If there are errors, then drop the table.

           BEGIN

             EXECUTE IMMEDIATE ''DROP TABLE identact'';

           EXCEPTION

            WHEN OTHERS THEN

              NULL;

            END;

        END;';

  TYPE containerListType IS TABLE OF VARCHAR2(128) INDEX BY PLS_INTEGER;

  containerList  containerListType;

BEGIN

  containerList(1) := 'PDB1';

  containerList(2) := 'PDB2';

  c1 := DBMS_SQL.OPEN_CURSOR;

  FOR conIndex IN containerList.first..containerList.last LOOP

    DBMS_OUTPUT.PUT_LINE('Creating in container: ' || containerList(conIndex));

    DBMS_SQL.PARSE(

      c => c1 ,

      statement                  => taskList,

      language_flag              => DBMS_SQL.NATIVE,

      edition                    => NULL,

      apply_crossedition_trigger => NULL,

      fire_apply_trigger         => NULL,

      schema                     => 'HR',

      container                  => containerList(conIndex));

     rowcount := DBMS_SQL.EXECUTE(c=>c1);

  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(c=>c1);

END;

/

Shutting Down a CDB Instance

You can shut down a CDB instance in the same way that you shut down a non-CDB instance.

Prerequisites

The following prerequisites must be met:

• The CDB instance must be mounted or open.
• The current user must be a common user with SYSDBA, SYSOPER, SYSBACKUP, or SYSDG administrative privilege. To shut down a CDB, you must exercise this privilege using AS SYSDBA, AS SYSOPER, AS SYSBACKUP, or AS SYSDG, respectively, at connect time.