Administering a CDB with SQL*Plus includes tasks such as access a container in a CDB, modifying a CDB, executing DDL statements in a CDB, and running Oracle-supplied SQL scripts in a CDB.
Administering a multitenant container database (CDB) is similar to administering a non-CDB, but there are some differences. Most of the differences are because some administrative tasks apply to the entire CDB, some apply only to the root, and some apply to specific pluggable databases (PDBs).
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
.
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.
Table 40-1 Administrative Tasks Common to CDBs and Non-CDBs
Task | Description | Additional Information |
---|---|---|
Starting up a CDB instance |
To start a CDB instance, the current user must be a common user whose current container is the root. When you open a CDB, its PDBs are mounted. Use the |
"Modifying the Open Mode of PDBs" "Modifying a PDB with the ALTER PLUGGABLE DATABASE Statement" |
Managing processes |
A CDB has one set of background processes shared by the root and all PDBs. |
|
Managing memory |
A CDB has a single system global area (SGA) and a single aggregate program global area (PGA). The memory required by a CDB is the sum of the memory requirements for all of the PDBs that will be part of the CDB. |
|
Managing security |
You can create and drop common users and local users in a CDB. You can also grant privileges to and revoke privileges from these users. You can also manage the In addition, grant the following roles to the appropriate users:
|
|
Monitoring errors and alerts |
A CDB has one alert log for the entire CDB. The name of a PDB is included in records in trace files, when appropriate. |
|
Managing diagnostic data |
In a CDB, you can use the Oracle Database fault diagnosability infrastructure and the Automatic Diagnostic Repository (ADR). |
|
Managing control files |
A CDB has one control file. |
|
Managing the online redo log and the archived redo log files |
A CDB has one online redo log and one set of archived redo log files. |
|
Managing tablespaces |
You can create, modify, and drop tablespaces and temporary tablespaces for the root and for individual PDBs. You can also specify a default tablespace, default tablespace type, and a default temporary tablespace for the root. The root has its own set of Oracle-supplied tablespaces, such as the |
|
Managing data files and temp files |
The root has its own data files, and each PDB has its own data files. In a CDB, you can manage data files and temp files in basically the same way you would manage them for a non-CDB. However, the following exceptions apply to CDBs:
|
|
Managing undo |
There is one active undo tablespace for a single-instance CDB. For an Oracle RAC CDB, there is one active undo tablespace for each instance. In a CDB, the Only a common user who has the appropriate privileges and whose current container is the root can create an undo tablespace. Undo tablespaces are visible in static data dictionary views and dynamic performance (V$) views when the current container is the root. Undo tablespaces are visible only in dynamic performance views when the current container is a PDB. When the current container is a PDB, an attempt to create an undo tablespace fails without returning an error. |
|
Moving data between PDBs |
You can move data between PDBs using the same methods that you would use to move data between non-CDBs. For example, you can transport the data or use Data Pump export/import to move the data. |
|
Using Oracle Managed Files |
Using Oracle Managed files can simplify administration for both a CDB and a non-CDB. |
|
Transparent Data Encryption is a feature that enables encryption of individual table columns before storing them in the data file, or enables encryption of entire tablespaces. In a CDB, each PDB has its own master key for Transparent Data Encryption, and, where applicable, the |
||
Oracle Data Guard can configure a physical standby or a logical standby of a CDB. Data Guard operates on the entire CDB, not on individual PDBs. |
||
Oracle Database Vault policies are scoped to individual PDBs. |
||
Dropping a database |
When you drop a CDB, the root, seed, and all of its PDBs (including their data) are also dropped. You can also drop individual PDBs with the |
See Also:
Oracle Database Concepts for more information about the architecture of 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.
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.
Table 40-2 Manageability Features in a CDB
Manageability Feature | Data Location | Data Visibility | Additional Information |
---|---|---|---|
Active Session History (ASH) ASH collects information about active database sessions. You can use this information to analyze and identify performance issues. |
Most of the ASH data is stored in memory. A small percentage of the ASH data samples are stored in the root. ASH data related to a PDB is not included if the PDB is unplugged. |
A common user whose current container is the root can view ASH data for the root and for PDBs. A user whose current container is a PDB can view ASH data for the PDB only. |
|
Alerts An alert is a notification of a possible problem. |
Threshold settings that pertain to a PDB are stored in the PDB. Alerts posted when thresholds are violated are enqueued into the alert queue in the root. Threshold settings that pertain to a PDB are included if the PDB is unplugged. Alerts related to a PDB are not included if the PDB is unplugged. |
A common user whose current container is the root can view alerts for the root and for PDBs. A user whose current container is a PDB can view alert thresholds and alerts for the PDB only. |
|
Automated Database Maintenance Tasks Automated database maintenance tasks are tasks that are started automatically at regular intervals to perform maintenance operations on the database. Automated tasks include automatic optimizer statistics collection, Automatic Segment Advisor tasks, and Automatic SQL Tuning Advisor tasks. |
A user can schedule maintenance windows and enable or disable maintenance tasks for the current container only. If the current container is the root, then the changes only apply to the root. If the current container is a PDB, then the changes only apply to the PDB. Data related to a PDB is stored in the PDB for automatic optimizer statistics collection and the Automatic Segment Advisor. This data is included if the PDB is unplugged. Automatic SQL Tuning Advisor runs only in the root. See the SQL Tuning Advisor row in this table for information about data collected by Automatic SQL Tuning Advisor. |
See the appropriate row in this table for data visibility information about the following manageability features: automatic optimizer statistics collection, Automatic Segment Advisor, and Automatic SQL Tuning Advisor. |
|
Automatic Database Diagnostic Monitor (ADDM) ADDM can diagnose a database's performance and determine how identified problems can be resolved. |
All ADDM runs must be performed in the root. All ADDM results are stored in the root. ADDM analyzes activity in a PDB within the context of the current analysis target. ADDM does not analyze one PDB at a time. As in previous releases, ADDM runs with a target of either the entire instance or Oracle RAC database. ADDM results related to a PDB are not included if the PDB is unplugged. |
ADDM results are visible only to a common user whose current container is the root. The ADDM results can include information about multiple PDBs. The ADDM results cannot be viewed when the current container is a PDB. |
|
Automatic Optimizer Statistics Collection Automatic optimizer statistics collection gathers optimizer statistics for all schema objects in the database for which there are no statistics or only stale statistics. The statistics gathered by this task are used by the SQL query optimizer to improve the performance of SQL execution. |
When an automatic optimizer statistics collection task gathers data for a PDB, it stores this data in the PDB. This data is included if the PDB is unplugged. |
A common user whose current container is the root can view optimizer statistics data for PDBs. A user whose current container is a PDB can view optimizer statistics data for the PDB only. |
|
Automatic Segment Advisor The Automatic Segment Advisor identifies segments that have space available for reclamation and makes recommendations on how to defragment those segments. |
When Automatic Segment Advisor gathers data for a PDB, it stores this data in the PDB. This data is included if the PDB is unplugged. |
A common user whose current container is the root can view Automatic Segment Advisor data for PDBs. A user whose current container is a PDB can view the Automatic Segment Advisor data for the PDB only. |
|
Automatic Workload Repository (AWR) The AWR collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is stored in the database. The gathered data can be displayed in both reports and views. |
AWR data is stored in the root. AWR data related to a PDB is not included if the PDB is unplugged. |
A common user whose current container is the root can view AWR data for the root and for PDBs. A user whose current container is a PDB can view AWR data for the PDB only. |
|
Database Replay Database Replay is a feature of Oracle Real Application Testing. Database Replay captures the workload for a database and replays it exactly on a test database. |
Information about database captures and replays are stored in the root. |
A common user whose current container is the root can view database capture and replay information. |
|
SQL Management Base (SMB) SMB stores statement logs, plan histories, SQL plan baselines, and SQL profiles in the data dictionary. |
SMB data related to a PDB is stored in the PDB. The SMB data related to a PDB is included if the PDB is unplugged. |
A common user whose current container is the root can view SMB data for PDBs. A user whose current container is a PDB can view the SMB data for the PDB only. |
|
SQL Performance Analyzer (SPA) SPA can analyze the SQL performance impact of SQL tuning and other system changes. SPA is often used with Database Replay. |
A common user whose current container is the root can run SPA for any PDB. In this case, the SPA results data is stored in the root and is not included if the PDB is unplugged. A user whose current container is a PDB can run SPA on the PDB. In this case, the SPA results data is stored in the PDB and is included if the PDB is unplugged. |
A common user whose current container is the root can view SPA results data for PDBs. A user whose current container is a PDB can view the SPA results data for the PDB only. |
|
SQL Tuning Sets (STS) An STS is a database object that includes one or more SQL statements along with their execution statistics and execution context, and could include a user priority ranking. You can use an STS to tune a group of SQL statements or test their performance using SPA. |
An STS can be stored in the root or in any PDB. If it is stored in the root, then you can load SQL statements from any PDB into it. When a PDB is unplugged, an STS stored in the root is not included, even if the STS contains SQL statements from the PDB. When a PDB is unplugged, an STS stored in the PDB is included. |
A common user whose current container is the root can view STS data stored in the root only. A user whose current container is a PDB can view STS data for the PDB only. |
|
SQL Tuning Advisor SQL Tuning Advisor optimizes SQL statements that have been identified as high-load SQL statements. |
Automatic SQL Tuning Advisor data is stored in the root. It might have results about SQL statements executed in a PDB that were analyzed by the advisor, but these results are not included if the PDB is unplugged. A common user whose current container is the root can run SQL Tuning Advisor manually for SQL statements from any PDB. When a statement is tuned, it is tuned in any container that runs the statement. A user whose current container is a PDB can also run SQL Tuning Advisor manually for SQL statements from the PDB. When SQL Tuning Advisor is run manually from a PDB, the results are stored in the PDB from which it is run. In this case, a statement is tuned only for the current PDB, and the results related to a PDB are included if the PDB is unplugged. |
When SQL Tuning Advisor is run automatically, the results are visible only to a common user whose current container is the root. These results cannot be viewed when the current container is a PDB. When SQL Tuning Advisor is run manually by a user whose current container is a PDB, the results are only visible to a user whose current container is that PDB. |
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
See Also:
"About CDB and PDB Information in Views" for an overview of container data objects
Oracle Database Security Guide for detailed information about container data objects
In a CDB, different containers can contain different database objects.
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.
See Also:
Oracle Database SQL Language Reference for information about schema objects and nonschema objects
Oracle Database Concepts for an overview of common users and local users
Oracle Database Security Guide for information about creating common users and local users
You can access a container in a CDB with SQL*Plus.
You can use SQL*Plus to access the root or a PDB in a CDB. You can connect to a container by using the SQL*Plus CONNECT
command, or you can switch into a container with an ALTER
SESSION
SET
CONTAINER
SQL statement.
This section assumes that you understand how to connect to a non-CDB in SQL*Plus. See "Submitting Commands and SQL to the Database" for information.
Clients access the root or a PDB through database services. Database services have an optional PDB
property. When a PDB is created, a new default service for the PDB is created automatically. The service has the same name as the PDB. With the service name, you can access the PDB using the easy connect syntax or the net service name from the tnsnames.ora file. Oracle Net Services must be configured properly for clients to access this service.
When a user connects using a service with a non-null PDB
property, the user name is resolved in the context of the specified PDB. When a user connects without specifying a service or using a service name with a null PDB
property, the user name is resolved in the context of the root. You can view the PDB
property for a service by querying the CDB_SERVICES
data dictionary view or by running the config
service
command in the SRVCTL utility.
The setting for the SESSIONS
initialization parameter limits the total number of sessions available in a CDB, including the sessions connected to PDBs. If the limit is reached for the CDB, then users cannot connect to PDBs. To ensure that one PDB does not use too many sessions, you can limit the number of sessions available to a PDB by setting the SESSIONS
initialization parameter in the PDB.
Note:
When two or more CDBs on the same computer system use the same listener and two or more PDBs have the same service name in these CDBs, a connection that specifies this service name connects randomly to one of the PDBs with the service name. To avoid incorrect connections, ensure that all service names for PDBs are unique on the computer system, or configure a separate listener for each CDB on the computer system.
See Also:
Oracle Database Concepts for more information about database services in a CDB
Oracle Database Net Services Administrator's Guide for information about configuring Oracle Net Services
"Listing the Initialization Parameters That Are Modifiable in PDBs"
You can use the SQL*Plus CONNECT
command to connect to the root or to a PDB.
You can connect to the root in the same way that you connect to a non-CDB.
Specifically, you can use the following techniques to connect to the root with the SQL*Plus CONNECT
command:
Local connection
Local connection with operating system authentication
Database connection using easy connect
Database connection using a net service name
Remote database connection using external authentication
The following prerequisites must be met for the user connecting to the root:
The user must be a common user.
The user must be granted CREATE
SESSION
privilege in the root.
To connect to the root using the SQL*Plus CONNECT command:
Configure your environment so that you can open SQL*Plus.
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.
Example 40-1 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
Example 40-2 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
Example 40-3 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##dba@mycdb
See Also:
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 change the open mode of a PDB, see "Modifying the Open Mode of PDBs".
To connect to a PDB using the SQL*Plus CONNECT command:
Configure your environment so that you can open SQL*Plus.
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.
Example 40-4 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 sh@salespdb
The following command connects to the SYSTEM
common user in the salespdb
PDB:
CONNECT system@salespdb
See "Step 4: Submit the SQL*Plus CONNECT Command" for more examples.
When you are connected to a container as a common user, you can switch to a different container using the ALTER SESSION
statement.
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.
Note:
When an ALTER
SESSION
SET
CONTAINER
statement is used to switch to the current container, these prerequisites are not enforced, and no error message is returned if they are not met.
To switch to a container using the ALTER SESSION statement:
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".
(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.
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
ALTER SESSION SET CONTAINER = salespdb;
Example 40-6 Switching to the Root
ALTER SESSION SET CONTAINER = CDB$ROOT;
Example 40-7 Switching to the Seed
ALTER SESSION SET CONTAINER = PDB$SEED;
See Also:
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.
Example 40-8 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; /
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_SQL
package
Oracle Database PL/SQL Language Reference for more information about autonomous transactions
You can modify the entire CDB, the root, or one or more PDBs.
The ALTER
DATABASE
statement modifies a CDB. The ALTER
PLUGGABLE
DATABASE
statement can modify the open mode of one or more PDBs.
Table 40-3 lists which containers are modified by clauses in ALTER
DATABASE
and ALTER
PLUGGABLE
DATABASE
statements.
Table 40-3 Statements That Modify Containers in a CDB
Modify Entire CDB | Modify Root Only | Modify One or More PDBs |
---|---|---|
When connected as a common user whose current container is the root, |
When connected as a common user whose current container is the root,
You can use these clauses to set non-default values for specific PDBs. |
When connected as a common user whose current container is the root, When the current container is a PDB, When connected as a common user whose current container is the root, |
This section contains the following topics:
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.
You can manage 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 each container in the CDB, including the root and each PDB.
A permanent tablespace can be associated with only one container. Therefore, a permanent tablespace can be associated with the root or with one PDB.
Each container in a CDB must have its own default permanent tablespace, and default permanent tablespaces cannot be shared between containers. Users connected to the container who are not explicitly assigned a tablespace use the default permanent tablespace for the container.
Each container in a CDB has its own default temporary tablespace (or tablespace group).
You also can create additional temporary tablespaces for individual containers, and you can assign specific users in containers to these temporary tablespaces. When you unplug a PDB, its temporary tablespaces are also unplugged.
When a user is not assigned a temporary tablespace explicitly in a container, the user’s temporary tablespace is the default temporary tablespace for the container.
You can use 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:
In SQL*Plus, ensure that the current container is the root.
Run an ALTER
DATABASE
statement with a clause that modifies an entire CDB.
Example 40-9 Backing Up the Control File for a CDB
This ALTER
DATABASE
statement uses a recovery_clause to back up a control file.
ALTER DATABASE BACKUP CONTROLFILE TO '+DATA/dbs/backup/control.bkp';
Example 40-10 Adding a Redo Log File to a CDB
This 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;
See Also:
You can use 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:
In SQL*Plus, ensure that the current container is the root.
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.
Example 40-11 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;
Example 40-12 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;
Example 40-13 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;
Example 40-14 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;
You can modify the open mode of a PDB by using the ALTER
PLUGGABLE
DATABASE
SQL statement or the SQL*Plus STARTUP
command.
A PDB can be in various modes.
Table 40-4 describes the possible PDB modes.
Table 40-4 PDB Modes
Mode | Description |
---|---|
|
A PDB in open read/write mode allows queries and user transactions to proceed and allows users to generate redo logs. |
|
A PDB in open read-only mode allows queries but does not allow user changes. |
|
When a PDB is in open migrate mode, you can run database upgrade scripts on the PDB. A PDB is in this mode after an |
|
When a PDB is in mounted mode, it behaves like a non-CDB in mounted mode. It does not allow changes to any objects, and it is accessible only to database administrators. It cannot read from or write to data files. Information about the PDB is removed from memory caches. Cold backups of the PDB are possible. |
The open read/write, read-only, and migrate modes can be restricted to users with RESTRICTED
SESSION
privilege in the PDB.
While a PDB is in mounted or read-only mode, database administrators can create, modify, or drop common users and roles in the CDB. The CDB applies these changes to the PDB when its open mode is changed to open in read/write mode. Before the changes are applied, descriptions of common users and roles in the PDB might be different from the descriptions in the rest of the CDB.
When a PDB is opened, Oracle Database checks the compatibility of the PDB with the CDB. Each compatibility violation is either a warning or an error. If a compatibility violation is a warning, then the warning is recorded in the alert log, but the PDB is opened normally without displaying a warning message. If a compatibility violation is an error, then a message is displayed when the PDB is opened stating that the PDB was altered with errors, and the errors are recorded in the alert log. You must correct the condition that caused each error. When there are errors, the PDB is opened, but access to the PDB is limited to users with RESTRICTED
SESSION
privilege so that the compatibility violations can be addressed. You can view descriptions of violations by querying PDB_PLUG_IN_VIOLATIONS
view.
See Also:
"Modifying the Open Mode of PDBs" for information about modifying the open mode of one or more PDBs when the current container is the root
"Modifying a PDB with the ALTER PLUGGABLE DATABASE Statement" for information about modifying the open mode of a PDB when the current container is the PDB
"Modifying a PDB" for information about modifying other attributes of a PDB
When the current container is the root, an ALTER
PLUGGABLE
DATABASE
statement with a pdb_change_state clause modifies the open mode of the specified PDBs.
The following table describes the clauses of the ALTER
PLUGGABLE
DATABASE
statement that modify the mode of a PDB.
Table 40-5 ALTER PLUGGABLE DATABASE Clauses That Modify the Mode of a PDB
Clause | Description |
---|---|
|
Opens the PDB in read/write mode. When When |
|
Opens the PDB in read-only mode. When When |
|
Opens the PDB in migrate mode. When |
|
Places the PDB in mounted mode. The When |
When you issue an ALTER
PLUGGABLE
DATABASE
OPEN
statement, READ
WRITE
is the default unless a PDB being opened belongs to a CDB that is used as a physical standby database, in which case READ
ONLY
is the default.
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 Real Application Clusters (Oracle RAC) CDB, you can use the instances clause to specify the instances on which the PDB is modified 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'] … )
Also, when you are closing a PDB in an Oracle RAC CDB, you can use the relocate clause, which includes the following options:
Specify NORELOCATE
, the default, to close the PDB in the current instance.
Specify RELOCATE
TO
and specify an instance name to reopen the PDB in the specified instance.
Specify RELOCATE
to reopen the PDB on a different instance that is selected by Oracle Database.
To modify the open mode of PDBs with the ALTER
PLUGGABLE
DATABASE
statement, the following prerequisites must be met:
The current user must have SYSDBA
, SYSOPER
, SYSBACKUP
, or SYSDG
administrative privilege, and the privilege must be either commonly granted or locally granted in the PDB. The user must exercise the privilege using AS
SYSDBA
, AS
SYSOPER
, AS
SYSBACKUP
, or AS
SYSDG
, respectively, at connect time.
When RESTRICTED
SESSION
is enabled, RESTRICTED
must be specified when a PDB is opened.
In an Oracle RAC CDB, if a PDB is open in one or more Oracle RAC instances, then it can be opened in additional instances, but it must be opened in the same mode as in the instances in which it is already open. A PDB can be closed in some instances and opened on others.
In addition, to place PDBs in a particular target mode with the ALTER
PLUGGAGLE
DATABASE
statement, you must meet the requirements described in the following table.
Table 40-6 Modifying the Open Mode of PDBs with ALTER PLUGGABLE DATABASE
Target Mode of PDBs | ALL Keyword Included | FORCE Keyword Included | Required Mode for the Root | Required Mode for Each PDB Being Modified |
---|---|---|---|---|
Read/write |
Yes |
Yes |
Read/write |
Mounted, read-only, or read/write |
Read/write |
Yes |
No |
Read/write |
Mounted or read/write |
Read/write |
No |
Yes |
Read/write |
Mounted, read-only, or read/write |
Read/write |
No |
No |
Read/write |
Mounted |
Read-only |
Yes |
Yes |
Read-only or read/write |
Mounted, read-only, or read/write |
Read-only |
Yes |
No |
Read-only or read/write |
Mounted or read-only |
Read-only |
No |
Yes |
Read-only or read/write |
Mounted, read-only, or read/write |
Read-only |
No |
No |
Read-only or read/write |
Mounted |
Migrate |
Yes |
Not applicable |
Read-only or read/write |
Mounted |
Migrate |
No |
Not applicable |
Read-only or read/write |
Mounted |
Mounted |
Yes |
Not applicable |
Read-only or read/write |
Mounted, read-only, migrate, or read/write |
Mounted |
No |
Not applicable |
Read-only or read/write |
Read-only, migrate, or read/write |
Note:
You can also modify the open mode of a PDB when the current container is the PDB. See "Modifying a PDB with the ALTER PLUGGABLE DATABASE Statement".
When the current container is the root, the STARTUP
PLUGGABLE
DATABASE
command can open a single PDB.
Use the following options of the STARTUP
PLUGGABLE
DATABASE
command to open a PDB:
FORCE
Closes an open PDB before re-opening it in read/write mode. When this option is specified, no other options are allowed.
RESTRICT
Enables only users with the RESTRICTED
SESSION
system privilege in the PDB to access the PDB.
If neither OPEN
READ
WRITE
nor OPEN
READ
ONLY
is specified, then the PDB is opened in read-only mode when the CDB to which it belongs is a physical standby database. Otherwise, the PDB is opened in read/write mode.
OPEN
open_pdb_options
Opens the PDB in either read/write mode or read-only mode. You can specify OPEN
READ
WRITE
or OPEN
READ
ONLY
. When you specify OPEN
without any other options, READ
WRITE
is the default.
The following prerequisites must be met:
The current user must have SYSDBA
, SYSOPER
, SYSBACKUP
, or SYSDG
administrative privilege, and the privilege must be either commonly granted or locally granted in the PDB. The user must exercise the privilege using AS
SYSDBA
, AS
SYSOPER
, AS
SYSBACKUP
, or AS
SYSDG
, respectively, at connect time.
When RESTRICTED
SESSION
is enabled, RESTRICT
must be specified when a PDB is opened.
In addition, to place PDBs in a particular target mode with the STARTUP
PLUGGAGLE
DATABASE
command, you must meet the requirements described in the following table.
Table 40-7 Modifying the Open Mode of a PDB with STARTUP PLUGGABLE DATABASE
Target Mode of the PDB | FORCE Option Included | Required Mode for the Root | Required Mode of the PDB Being Modified |
---|---|---|---|
Read/write |
Yes |
Read/write |
Mounted, read-only, or read/write |
Read/write |
No |
Read/write |
Mounted |
Read-only |
No |
Read-only or read/write |
Mounted |
Note:
You can also use the STARTUP
command to modify the open mode of a PDB when the current container is the PDB. See "Using the STARTUP SQL*Plus Command on a PDB".
You can modify the open mode of PDBs with the ALTER
PLUGGABLE
DATABASE
statement with a pdb_change_state clause.
In SQL*Plus, ensure that the current container is the root.
Run an ALTER
PLUGGABLE
DATABASE
statement with a pdb_change_state clause.
Example 40-15 Changing the Open Mode of Listed PDBs
This statement changes the open mode of PDBs salespdb
and hrpdb
to open in read/write mode.
ALTER PLUGGABLE DATABASE salespdb, hrpdb OPEN READ WRITE;
This statement changes the open mode of PDB salespdb
to open in read-only mode. RESTRICTED
specifies that the PDB is accessible only to users with RESTRICTED
SESSION
privilege in the PDB.
ALTER PLUGGABLE DATABASE salespdb OPEN READ ONLY RESTRICTED;
This statement changes the open mode of PDB salespdb
to open in migrate mode:
ALTER PLUGGABLE DATABASE salespdb OPEN UPGRADE;
Example 40-16 Changing the Open Mode of All PDBs
Run the following query to display the open mode of each PDB associated with a CDB:
SELECT NAME, OPEN_MODE FROM V$PDBS WHERE CON_ID > 2; NAME OPEN_MODE ------------------------------ ---------- HRPDB READ WRITE SALESPDB MOUNTED DWPDB MOUNTED
Notice that hrpdb
is already in read/write mode. To change the open mode of salespdb
and dwpdb
to open in read/write mode, use the following statement:
ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;
The hrpdb
PDB is not modified because it is already in open read/write mode. The statement does not return an error because two PDBs are in mounted mode and one PDB (hrpdb
) is in the specified mode (read/write). Similarly, the statement does not return an error if all PDBs are in mounted mode.
However, if any PDB is in read-only mode, then the statement returns an error. To avoid an error and open all PDBs in the CDB in read/write mode, specify the FORCE
keyword:
ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE FORCE;
With the FORCE
keyword included, all PDBs are opened in read/write mode, including PDBs in read-only mode.
Example 40-17 Changing the Open Mode of All PDBs Except for Listed Ones
This statement changes the mode of all PDBs except for salespdb
and hrpdb
to mounted mode.
ALTER PLUGGABLE DATABASE ALL EXCEPT salespdb, hrpdb CLOSE IMMEDIATE;
Note:
An ALTER
PLUGGABLE
DATABASE
statement modifying the open mode of a PDB is instance-specific. Therefore, if this statement is issued when connected to an Oracle RAC instance, then it affects the open mode of the PDB only in that instance.
See Also:
"About Modifying the Open Mode of PDBs with ALTER PLUGGABLE DATABASE"
"Modifying a PDB" for information about modifying the other attributes of a PDB
"Altering Database Availability" for information about database modes and their uses
Oracle Database Concepts for more information about shutdown modes
You can use the STARTUP
PLUGGABLE
DATABASE
command to open a single PDB.
To modify a PDB with the STARTUP
PLUGGABLE
DATABASE
command:
In SQL*Plus, ensure that the current container is the root.
Run the STARTUP
PLUGGABLE
DATABASE
command.
Note:
When the current container is the root, the SQL*Plus SHUTDOWN
command always shuts down the CDB instance. It cannot be used to close individual PDBs.
Example 40-18 Opening a PDB in Read/Write Mode with the STARTUP Command
STARTUP PLUGGABLE DATABASE hrpdb OPEN
Example 40-19 Opening a PDB in Read/Write Restricted Mode with the STARTUP Command
STARTUP PLUGGABLE DATABASE hrpdb RESTRICT
Example 40-20 Opening a PDB in Read-Only Restricted Mode with the STARTUP Command
STARTUP PLUGGABLE DATABASE hrpdb OPEN READ ONLY RESTRICT
Example 40-21 Opening a PDB in Read-Only Mode with the STARTUP Command
STARTUP PLUGGABLE DATABASE hrpdb OPEN READ ONLY
Example 40-22 Opening a PDB in Read/Write Mode with the STARTUP Command and the FORCE Option
This example assumes that the hrpdb
PDB is currently open. The FORCE
option closes the PDB and then opens it in the read/write mode.
STARTUP PLUGGABLE DATABASE hrpdb FORCE
See Also:
"About Modifying the Open Mode of PDBs with the SQL*Plus STARTUP Command"
"Modifying a PDB with the SQL*Plus STARTUP and SHUTDOWN Commands" for information about using the STARTUP
or SHUTDOWN
command when the current container is a PDB
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.
Note:
This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).
You can do this 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:
In SQL*Plus, ensure that the current container is the root.
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.
Example 40-23 Preserving the Open Mode of a PDB When the CDB Restarts
This statement preserves the open mode of the salespdb
when the CDB restarts.
ALTER PLUGGABLE DATABASE salespdb SAVE STATE;
Example 40-24 Discarding the Open Mode of a PDB When the CDB Restarts
This statement discards the open mode of the salespdb
when the CDB restarts.
ALTER PLUGGABLE DATABASE salespdb DISCARD STATE;
Example 40-25 Preserving the Open Mode of All PDBs When the CDB Restarts
This statement preserves the open mode of all of the PDBs when the CDB restarts.
ALTER PLUGGABLE DATABASE ALL SAVE STATE;
Example 40-26 Preserving the Open Mode of Listed PDBs When the CDB Restarts
This statement preserves the open mode of the salespdb
and hrpdb
when the CDB restarts.
ALTER PLUGGABLE DATABASE salespdb, hrpdb SAVE STATE;
Example 40-27 Preserving the Open Mode of All PDBs Except for Listed Ones When the CDB Restarts
This statement preserves the open mode of all PDBs except for salespdb
and hrpdb
.
ALTER PLUGGABLE DATABASE ALL EXCEPT salespdb, hrpdb SAVE STATE;
The ALTER
SYSTEM
SET
statement can dynamically set an initialization parameter in one or more containers in a CDB.
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 particular parameter in the root applies to a particular PDB.
A PDB can override the root's setting for some parameters, which means that 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. The inheritance property is false for a parameter when the PDB does not inherit the root's value for the parameter.
The inheritance property for some parameters must be true. For other parameters, you can change the inheritance property by running the ALTER
SYSTEM
SET
statement to set the parameter when the current container is the PDB. If ISPDB_MODIFIABLE
is TRUE
for an initialization parameter in the V$SYSTEM_PARAMETER
view, 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 default, means that the parameter setting applies only to the current 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
means that the parameter setting applies to all containers in the CDB, including the root and all of the PDBs.
Specifying ALL
sets the inheritance property to true for the parameter in all PDBs.
See "About the Current Container" for more information about the CONTAINER
clause and rules that apply to it.
To use ALTER
SYSTEM
SET
in the root in a CDB:
In SQL*Plus, ensure that the current container is the root.
The current user must have the commonly granted ALTER
SYSTEM
privilege.
Run the ALTER
SYSTEM
SET
statement.
Note:
If you want to change the inheritance property for a particular parameter in a particular PDB from false to true, then you can run the ALTER
SYSTEM
RESET
statement to reset the parameter when the current container is the PDB. The following example resets the OPEN_CURSORS
parameter:
ALTER SYSTEM RESET OPEN_CURSORS SCOPE = SPFILE;
Example 40-28 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;
Example 40-29 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;
See Also:
Oracle Database SQL Language Reference for more information about the ALTER
SYSTEM
SET
statement
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 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-8.
Table 40-8 DDL Statements and the CONTAINER Clause in a CDB
DDL Statement | CONTAINER = CURRENT | CONTAINER = ALL |
---|---|---|
|
Creates a local user in the current PDB. |
Creates a common user. |
|
Alters a local user in the current PDB. |
Alters a common user. |
|
Creates a local role in the current PDB. |
Creates a common role. |
|
Grants a privilege in the local container to a local user, common user, or local role. The |
Grants a system privilege or object privilege on a common object to a common user or common role. The specified privilege is granted to the user or role across the entire CDB. |
|
Revokes a privilege in the local container from a local user, common user, or local role. This statement can revoke only a privilege granted with The |
Revokes a system privilege or object privilege on a common object from a common user or common role. The specified privilege is revoked from the user or role across the entire CDB. This statement can revoke only a privilege granted with |
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##
.
See Also:
Oracle Database Security Guide for more information about managing users in a CDB
Oracle Database Reference for more information about the COMMON_USER_PREFIX
initialization parameter
"Using the ALTER SYSTEM SET Statement in a CDB" for information about using the ALTER
SYSTEM
system control statement in a CDB
Specify CURRENT
in the CONTAINER
clause of a DDL statement listed in to execute the statement in the current container.
The supported DDL statements are listed in Table 40-8.
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:
In SQL*Plus, access a container.
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, the prefix is C##
or c##
. The specified tablespace must exist in the PDB.
Example 40-30 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;
Specify ALL
in the CONTAINER
clause of a DDL statement to execute the statement in all of the containers in a CDB.
The supported DDL statements are listed in Table 40-8.
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:
In SQL*Plus, ensure that the current container is the root.
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, 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.
Example 40-31 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;
You can use the catcon.pl script to run Oracle-supplied SQL scripts in a CDB.
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.
Note:
Unless you exclude the seed when you run catcon.pl, the SQL script or SQL statement is run on the seed.
You can use the catcon.pl script to run scripts on both CDBs and non-CDBs.
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 --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
.
Table 40-9 describes the catcon.pl parameters.
Table 40-9 catcon.pl Parameters
Parameter | Description |
---|---|
|
(Optional) Specifies the username and password to connect to the root and the specified PDBs. Specify a common user with the required privileges to run the SQL script or the SQL statement. The default is " |
|
(Optional) Specifies the username and password to connect to the root and the specified PDBs. Specify a common user with the required privileges to perform internal tasks, such as running queries on the CDB's metadata. The default is " |
|
(Optional) Directory that contains the SQL script. The default is the current directory. |
|
(Optional) Directory into which catcon.pl writes log files. The default is the current directory. |
|
(Optional) The containers in which the SQL script is run or is not run. The The Specify containers in a space-delimited list of PDB names enclosed in single quotation marks. The |
|
(Optional) Integer that specifies the degree of parallelism. This parameter specifies the current number of invocations of the catcon.pl script on the host. |
|
(Optional) Sets echo |
|
(Optional) Spools the output of every script into a file with the following name: log-file-name-base_script-name-without-extension_[container-name-if-any].default-extension |
|
(Optional) When set to When a table other than See SQL*Plus User's Guide and Reference for more information about the error logging table. |
|
(Optional) Do not issue a |
|
(Optional) Turns on the generation of debugging information. |
|
(Optional) Ignore PDBs that are closed or, if the When this option is not specified and some specified PDBs do not exist or are not open, an error is returned and none of the containers are processed. |
|
(Mandatory) The base name for log file names. |
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. See "Modifying the Open Mode of PDBs".
To run the catcon.pl script:
Open a command line prompt.
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
This example runs the catblock.sql script in all of the containers of 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
The following parameters are specified:
The -u
parameter specifies that SYS
user runs the script in each container.
The -d
parameter specifies that the SQL script is in the $ORACLE_HOME/rdbms/admin directory.
The -b
parameter specifies that the base name for log file names is catblock_output.
Default parameter values are used for all other parameters. Neither the -c
nor the -C
parameter is specified. Therefore, catcon.pl runs the script in all containers by default.
Example 40-33 Running the catblock.sql Script in Specific PDBs
This 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 -u SYS -U SYS -d $ORACLE_HOME/rdbms/admin -l '/disk1/script_output' -c 'HRPDB SALESPDB' -b catblock_output catblock.sql
The following parameters are specified:
The -u
parameter specifies that SYS
user runs the script in each container.
The -U
parameter specifies that SYS
user performs internal tasks.
The -d
parameter specifies that the SQL script is in the $ORACLE_HOME/rdbms/admin directory.
The -l
parameter specifies that the output files are placed in the /disk1/script_output directory.
The -c
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 -b
parameter specifies that the base name for log file names is catblock_output.
Example 40-34 Running the catblock.sql Script in All Containers Except for Specific PDBs
This 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 -u SYS -d $ORACLE_HOME/rdbms/admin -l '/disk1/script_output' -C 'HRPDB SALESPDB' -b catblock_output catblock.sql
The following parameters are specified:
The -u
parameter specifies that SYS
user runs the script in each container.
The -d
parameter specifies that the SQL script is in the $ORACLE_HOME/rdbms/admin directory.
The -l
parameter specifies that the output files are placed in the /disk1/script_output directory.
The -C
parameter specifies that the SQL script is run in all of the containers in the CDB except for the hrpdb
and salespdb
PDBs.
The -b
parameter specifies that the base name for log file names is catblock_output.
Example 40-35 Running a SQL Script with Command Line Parameters
This example runs the custom_script.sql script in all of the containers of a CDB.
$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:'
The following parameters are specified:
The -u
parameter specifies that SYS
user runs the script in each container.
The -d
parameter specifies that the SQL script is in the /u01/scripts directory.
The -b
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 -c
nor the -C
parameter is specified. Therefore, catcon.pl runs the script in all containers by default.
Example 40-36 Running a SQL Statement in All Containers in a CDB
This example runs a SQL statement in all of the containers of a CDB.
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -e -b select_output -- --x"SELECT * FROM DUAL"
The following parameters are specified:
The -u
parameter specifies that SYS
user runs the script in each container.
The -e
parameter shows output for the SQL statement.
The -b
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 single-letter parameter (-b
), it must be preceded by --
.
Default parameter values are used for all other parameters. Neither the -c
nor the -C
parameter is specified. Therefore, catcon.pl runs the SQL statement in all containers by default.
See Also:
"Monitoring Locks" for information about the catblock.sql script
Oracle Database SQL Language Reference for more information about SQL scripts
You can shut down a CDB instance in the same way that you shut down a non-CDB instance.
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.
To shut down a CDB:
In SQL*Plus, ensure that the current container is the root.
See "Connecting to a Container Using the SQL*Plus CONNECT Command".
Shut down the CDB instance.
See Also: