40 Administering a CDB with SQL*Plus

About the 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 root or a PDB. Each session has exactly one current container at any point in time, but it is possible for a session to 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 in a CDB. 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 (root) only for common users. The current container can be a particular PDB for both common users and local users.

• The current container must be the 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.

• Only a common user with the commonly granted SET CONTAINER privilege can run a SQL statement that includes CONTAINER = ALL.

About 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 root and any 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. Table 40-1 describes some of these tasks and provides pointers to the relevant documentation.

About Using Manageability Features in a CDB

It is important to understand where the data is stored for Oracle Database's manageability features in a CDB. When the data for a feature is stored in the root only, the data related to a PDB is not included if the PDB is unplugged. However, when the data for a feature is stored in the PDB, the data related to the PDB is included if the PDB is unplugged, and this data remains part of the PDB if it is plugged into the same CDB or a different CDB.

It is also important to understand which data is visible to different users. Generally, in a CDB, a common user can view data for the root and for multiple PDBs when the common user's current container is the 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.

Table 40-2 describes how the manageability features work 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

About Managing Database Objects in a CDB

An Oracle database stores database objects, such as tables, indexes, and directories. Database objects that are owned by a schema are called schema objects, while database objects that are not owned by a schema are called nonschema objects. The root and PDBs contain schemas, and schemas contain schema objects. The root and PDBs can also contain nonschema objects, such as users, roles, tablespaces, directories, and editions.

In a CDB, the root contains Oracle-supplied schemas and database objects. Oracle-supplied common users, such as SYS and SYSTEM, own these schemas and common database objects. They can also own local objects, both in the root and in a PDB.

You can create common users in the root to administer containers in the CDB. User-created common users can create database objects in the root. Oracle recommends that, in the root, schemas owned by user-created common users contain only database triggers and the objects used in their definitions. A user-created common user can also own any type of local object in a PDB.

You can create local users in a PDB. A local user in a PDB can create schema objects and nonschema objects in the PDB. You cannot create local users in the root.

Name resolution in a CDB is similar to name resolution in a non-CDB, except that names are resolved in the context of the dictionary of the user's current container.

Connecting to a Container Using the SQL*Plus CONNECT Command

This section describes using the SQL*Plus CONNECT command to connect to the root or to a PDB.

This section contains the following topics:

• Connecting to the Root Using the SQL*Plus CONNECT Command

• Connecting to a PDB Using the SQL*Plus CONNECT Command

connect system

connect / as sysdba

connect c##dba@mycdb

CONNECT sh@salespdb

CONNECT system@salespdb

Switching to a Container Using the ALTER SESSION Statement

When you are connected to a container as a common user, you can use the following statement to switch to a different container:

ALTER SESSION SET CONTAINER = container_name

For container_name, specify one of the following:

• CDB$ROOT to switch to the root

• PDB$SEED to switch to the 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.

The following are considerations for using the ALTER SESSION SET CONTAINER statement:

• After the statement completes successfully, the current schema of the session is set to the schema owned by the common user in the specified container.

• After the statement completes successfully, the security context is reset to that of the schema owned by the common user in the specified container.

• After the statement completes successfully, login triggers for the specified container do not fire.

  If you require a trigger, then you can define a before or after SET CONTAINER trigger in a PDB to fire before or after the ALTER SESSION SET CONTAINER statement is executed.

• Package states are not shared across containers.

• When closing a PDB, sessions that switched into the PDB and sessions that connected directly to the PDB are handled identically.

• A transaction cannot span multiple containers. If you start a transaction and use ALTER SESSION SET CONTAINER to switch to a different container, then you cannot issue DML, DDL, COMMIT, or ROLLBACK statements until you switch back to the container in which you started the transaction.

• If you open a cursor and use ALTER SESSION SET CONTAINER to switch to different container, then you cannot fetch data from that cursor until you switch back to the container in which the cursor was opened.

• You can use the ALTER SESSION SET CONTAINER statement for connection pooling as well as advanced CDB administration.

  For example, you can use this statement for connection pooling with PDBs for a multitenancy application. A multitenancy application uses a single instance of the software on a server to serve multiple customers (tenants). In a non-CDB, multitenancy is typically supported by adding an extra column that identifies the tenant to every table used by the application, and tenants check out connections from a connection pool. In a CDB with PDBs, each tenant can have its own PDB, and you can use the ALTER SESSION SET CONTAINER statement in a connection pooling configuration.

The following prerequisites must be met to use the ALTER SESSION SET CONTAINER statement:

• The current user must be a common user. The initial connection must be made using the SQL*Plus CONNECT command.

• When altering a session to switch to a PDB as a common user that was not supplied with Oracle Database, the current user must be granted the SET CONTAINER privilege commonly or must be granted this privilege locally in the PDB.

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.

   See "Connecting to a Container Using the SQL*Plus CONNECT Command".

2. (Optionally) 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.

   If the open mode of the root should be changed, then follow the instructions in "Altering Database Availability" to change the open mode.

   If the open mode of the PDB should be changed, then follow the instructions in "Modifying the Open Mode of PDBs" to change the open mode.

   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.

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

The following examples switch to various containers using ALTER SESSION:

• Example 40-5, "Switching to the PDB salespdb"

• Example 40-6, "Switching to the Root"

• Example 40-7, "Switching to the Seed"

ALTER SESSION SET CONTAINER = salespdb;

ALTER SESSION SET CONTAINER = CDB$ROOT;

ALTER SESSION SET CONTAINER = PDB$SEED;

About the Statements That Modify a CDB

When connected as a common user whose current container is the root, the ALTER DATABASE statement works the same in a CDB and a non-CDB. Most ALTER DATABASE statements affect the entire CDB. The exceptions are listed in the "Modify Root Only" column of Table 40-3.

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.

When connected as a common user whose current container is the root, the ALTER PLUGGABLE DATABASE statement with the pdb_change_state clause modifies the open mode of one or more PDBs.

When the current container is a PDB, ALTER DATABASE and ALTER PLUGGABLE DATABASE statements modify the current PDB only.

About Managing Tablespaces in a CDB

A tablespace is a logical storage container for database objects, such as tables and indexes, that consume storage space. At the physical level, a tablespace stores data in one or more data files or temp files. You can use the ALTER DATABASE statement to manage tablespaces in a CDB.

The following are considerations for tablespaces in a CDB:

• A permanent tablespace can be associated with only one container.

• When you create a tablespace in a container, the tablespace is associated with that container.

• A CDB has one active undo tablespace, or one active undo tablespace for each instance of an Oracle RAC CDB.

• There is one default temporary tablespace for an entire CDB. The root and the PDBs can use this temporary tablespace. A PDB can also have its own temporary tablespaces.

Modifying an Entire CDB

This section describes using the ALTER DATABASE statement to modify an entire CDB, including the root and all of the PDBs. Most ALTER DATABASE statements modify the entire CDB. See the "Modify Entire CDB" column of Table 40-3 for a list of these statements.

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 run 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.

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

2. Run an ALTER DATABASE statement with a clause that modifies an entire CDB.

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

ALTER DATABASE cdb ADD LOGFILE
  GROUP 4 ('/u01/logs/orcl/redo04a.log','/u02/logs/orcl/redo04b.log')
  SIZE 100M BLOCKSIZE 512 REUSE;

Modifying the Root

This section describes using the ALTER DATABASE statement to modify only the root of a CDB. When the current container is the root, these ALTER DATABASE statements modify the root without directly modifying any of the PDBs. See the "Modify Root Only" column of Table 40-3 for a list of these statements.

Some of these 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.

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.

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

2. Run an ALTER DATABASE statement with a clause that modifies the root.

The following examples modify the root:

• Example 40-11, "Changing the Default Permanent Tablespace for the Root"

• Example 40-12, "Bringing a Data File Online for the Root"

• Example 40-13, "Changing the Default Tablespace Type for the Root"

• Example 40-14, "Changing the Default Temporary Tablespace for the Root"

ALTER DATABASE DEFAULT TABLESPACE root_tbs;

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.

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

ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;

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

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE cdb_temp;

The tablespace or tablespace group specified in the ALTER DATABASE statement must exist in the root. This tablespace or tablespace group is also the default temporary tablespace for PDBs.

Modifying the Open Mode of PDBs

You can modify the open mode of a PDB by using the ALTER PLUGGABLE DATABASE SQL statement or the SQL*Plus STARTUP command.

This section contains the following topics:

• About the Open Mode of a PDB

• Modifying the Open Mode of PDBs with ALTER PLUGGABLE DATABASE

• Modifying the Open Mode of PDBs with the SQL*Plus STARTUP Command

ALTER PLUGGABLE DATABASE salespdb, hrpdb
   OPEN READ WRITE;

ALTER PLUGGABLE DATABASE salespdb
   OPEN READ ONLY RESTRICTED;

ALTER PLUGGABLE DATABASE salespdb
   OPEN UPGRADE;

SELECT NAME,OPEN_MODE FROM V$PDBS WHERE CON_ID > 2;
 
NAME                           OPEN_MODE
------------------------------ ----------
HRPDB                          READ WRITE
SALESPDB                       MOUNTED
DWPDB                          MOUNTED

ALTER PLUGGABLE DATABASE ALL
   OPEN READ WRITE;

ALTER PLUGGABLE DATABASE ALL
   OPEN READ WRITE FORCE;

ALTER PLUGGABLE DATABASE ALL EXCEPT salespdb, hrpdb
   CLOSE IMMEDIATE;

STARTUP PLUGGABLE DATABASE hrpdb OPEN

STARTUP PLUGGABLE DATABASE hrpdb RESTRICT

STARTUP PLUGGABLE DATABASE hrpdb OPEN READ ONLY RESTRICT

STARTUP PLUGGABLE DATABASE hrpdb OPEN READ ONLY

STARTUP PLUGGABLE DATABASE hrpdb FORCE

Preserving or Discarding the Open Mode of PDBs When the CDB Restarts

You can preserve the open mode of one or more PDBs when the CDB restarts by using the ALTER PLUGGABLE DATABASE SQL statement with a pdb_save_or_discard_state clause in the following way:

• Specify SAVE STATE to preserve the PDBs' mode when the CDB is restarted.

  For example, if a PDB is in open read/write mode before the CDB is restarted, then the PDB is in open read/write mode after the CDB is restarted; if a PDB is in mounted mode before the CDB is restarted, then the PDB is in mounted mode after the CDB is restarted.

• Specify DISCARD STATE to ignore the PDBs' open mode when the CDB is restarted.

  When DISCARD STATE is specified for a PDB, the PDB is always mounted after the CDB is restarted.

You can specify which PDBs to modify in the following ways:

• List one or more PDBs.

• Specify ALL to modify all of the PDBs.

• Specify ALL EXCEPT to modify all of the PDBs, except for the PDBs listed.

For an Oracle RAC CDB, you can use the instances clause in the pdb_save_or_discard_state clause to specify the instances on which a PDB's open mode is preserved in the following ways:

• List one or more instances in the instances clause in the following form:

  INSTANCES = ('instance_name' [,'instance_name'] … )
  

• Specify ALL in the instances clause to modify the PDB in all running instances, as in the following example:

  INSTANCES = ALL
  

• Specify ALL EXCEPT in the instances clause to modify the PDB in all of the instances, except for the instances listed, in the following form:

  INSTANCES = ALL EXCEPT('instance_name' [,'instance_name'] … )
  

For a PDB in an Oracle RAC CDB, SAVE STATE and DISCARD STATE only affect the mode of the current instance. They do not affect the mode of other instances, even if more than one instance is specified in the instances clause.

To issue an ALTER PLUGGABLE DATABASE SQL statement with a pdb_save_or_discard_state clause, the current user must have the ALTER DATABASE privilege in the root.

You can check the saved states for the PDBs in a CDB by querying the DBA_PDB_SAVED_STATES view.

To preserve or discard a PDB's open mode when the CDB restarts: 

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

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

2. Run an ALTER PLUGGABLE DATABASE statement with a pdb_save_or_discard_state clause.

The following examples either preserve or discard the open mode of one or more PDBs when the CDB restarts:

• Preserving the Open Mode of a PDB When the CDB Restarts

• Discarding the Open Mode of a PDB When the CDB Restarts

• Preserving the Open Mode of All PDBs When the CDB Restarts

• Preserving the Open Mode of Listed PDBs When the CDB Restarts

• Preserving the Open Mode of All PDBs Except for Listed Ones When the CDB Restarts

ALTER PLUGGABLE DATABASE salespdb SAVE STATE;

ALTER PLUGGABLE DATABASE salespdb DISCARD STATE;

ALTER PLUGGABLE DATABASE ALL SAVE STATE;

ALTER PLUGGABLE DATABASE salespdb, hrpdb SAVE STATE;

ALTER PLUGGABLE DATABASE ALL EXCEPT salespdb, hrpdb SAVE STATE;

About Executing DDL Statements in a CDB

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 of the PDBs.

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

• The restrictions described in "About the Current Container".

• You can use the CONTAINER clause only with the DDL statements listed in Table 40-9.

All other DDL statements apply to the current container only.

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

• It is best practice for common user and role names to start with a prefix to avoid naming conflicts between common users and roles and local users and roles. You specify this prefix with the COMMON_USER_PREFIX initialization parameter. By default, the prefix is C## or c##.

• Common user and role names must consist only of ASCII characters.

• Local user and role names must not start with the prefix specified for common users with the COMMON_USER_PREFIX initialization parameter.

• Local user and role names must not start with C## or c##.

Executing a DDL Statement in the Current Container

Specify CURRENT in the CONTAINER clause of a DDL statement listed in Table 40-9 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.

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

2. Execute the DDL statement with CONTAINER set to CURRENT.

CREATE USER testpdb IDENTIFIED BY password 
   DEFAULT TABLESPACE pdb1_tbs
   QUOTA UNLIMITED ON pdb1_tbs
   CONTAINER = CURRENT;

A local user's user name cannot start with the prefix specified by the COMMON_USER_PREFIX initialization parameter. By default, the prefix is C## or c##. The specified tablespace must exist in the PDB.

Executing a DDL Statement in All Containers in a CDB

Specify ALL in the CONTAINER clause of a DDL statement listed in Table 40-9 to execute the statement in all of the containers in a CDB.

The following prerequisites must be met:

• The current user must be a common user.

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

To execute a DDL statement in all 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. Execute the DDL statement with CONTAINER set to ALL.

CREATE USER c##testcdb IDENTIFIED BY password 
   DEFAULT TABLESPACE cdb_tbs
   QUOTA UNLIMITED ON cdb_tbs
   CONTAINER = ALL;

A common user's user name must start with the prefix specified by the COMMON_USER_PREFIX initialization parameter. By default, the prefix is C## or c##. 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.

About Running Oracle-Supplied SQL Scripts in a CDB

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

In a CDB, the catcon.pl script is the best way to run SQL scripts and SQL statements. It can run them 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  
[-u username[/password]] [-U username[/password]] [-d directory] 
[-l directory] [{-c|-C} container] [-p parallelism] [-e] [-s] 
[-E { ON | errorlogging-table-other-than-SPERRORLOG } ] [-I] [-g] [-f] 
-b log_file_name_base -- { SQL_script [arguments] | --x'SQL_statement' }

Ensure that --xSQL_statement is preceded by -- if it follows any single-letter parameter. If --xSQL_statement is preceded by a script name or another --xSQL_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.

Table 40-10 describes the catcon.pl parameters.

log-file-name-base_script-name-without-extension_[container-name-if-any].default-extension

Running the catcon.pl Script

This section describes running the catcon.pl Perl script and provides examples that run the 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. See "Modifying the Open Mode of PDBs".

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:

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

Examples That Run the catcon.pl Script

The following examples run the catcon.pl script:

• Example 40-32, "Running the catblock.sql Script in All Containers in a CDB"

• Example 40-33, "Running the catblock.sql Script in Specific PDBs"

• Example 40-34, "Running the catblock.sql Script in All Containers Except for Specific PDBs"

• Example 40-35, "Running a SQL Script with Command Line Parameters"

• Example 40-36, "Running a SQL Statement in All Containers in a CDB"

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS 
-d $ORACLE_HOME/rdbms/admin -b catblock_output catblock.sql

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -U SYS 
-d $ORACLE_HOME/rdbms/admin -l '/disk1/script_output' -c 'HRPDB SALESPDB' 
-b catblock_output catblock.sql

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS 
-d $ORACLE_HOME/rdbms/admin -l '/disk1/script_output' -C 'HRPDB SALESPDB' 
-b catblock_output catblock.sql

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS 
-d /u01/scripts -b custom_script_output custom_script.sql
'--phr' '--PEnter password for user hr:'

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -e 
-b select_output -- --x'SELECT * FROM DUAL'