Skip Headers
Oracle® Database SQL Language Reference
12c Release 1 (12.1)

E17209-15
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

ALTER PLUGGABLE DATABASE

Purpose

Use the ALTER PLUGGABLE DATABASE statement to modify a pluggable database (PDB).

This statement enables you to perform the following tasks:

  • Unplug a PDB from a multitenant container database (CDB) (using the pdb_unplug_clause)

  • Modify the settings of a PDB (using the pdb_settings_clauses)

  • Bring PDB data files online or take them offline (using the pdb_datafile_clause)

  • Back up and recover a PDB (using the pdb_recovery_clauses)

  • Modify the state of a PDB (using the pdb_change_state clause)

  • Modify the state of multiple PDBs within a CDB (using the pdb_change_state_from_root clause)

Note:

You can perform all ALTER PLUGGABLE DATABASE tasks by connecting to a PDB and running the corresponding ALTER DATABASE statement. This functionality is provided to maintain backward compatibility for applications that have been migrated to a CDB environment. The exception is modifying PDB storage limits, for which you must use the pdb_storage_clause of ALTER PLUGGABLE DATABASE.

See Also:

CREATE PLUGGABLE DATABASE for information on creating PDBs

Prerequisites

You must be connected to a CDB.

To specify the pdb_unplug_clause, the current container must be the root, you must be authenticated AS SYSDBA or AS SYSOPER, and the SYSDBA or SYSOPER privilege must be either granted to you commonly, or granted to you locally in the root and locally in the PDB you want to unplug.

To specify the pdb_settings_clauses, the current container must be the PDB whose settings you want to modify and you must have the ALTER DATABASE privilege, either granted commonly or granted locally in the PDB. To specify the RENAME GLOBAL_NAME clause, you must also have the RESTRICTED SESSION privilege, either granted commonly or granted locally in the PDB being renamed, and the PDB must be in READ WRITE RESTRICTED mode.

To specify the pdb_datafile_clause, you must have the ALTER DATABASE privilege, either granted commonly or granted locally in the PDB whose datafiles you want to bring online or take offline. The current container must be the root or the PDB.

To specify the pdb_recovery_clauses, the current container must be the PDB you want to back up or recover and you must have the ALTER DATABASE privilege, either granted commonly or granted locally in the PDB.

To specify the pdb_change_state clause, the current container must be the PDB whose state you want to change and you must be authenticated AS SYSBACKUP, AS SYSDBA, AS SYSDG, or AS SYSOPER.

To specify the pdb_change_state_from_root clause, the current container must be the root, you must be authenticated AS SYSBACKUP, AS SYSDBA, AS SYSDG, or AS SYSOPER, and the SYSBACKUP, SYSDBA, SYSDG, or SYSOPER privilege must be either granted to you commonly, or granted to you locally in the root and locally in the PDB(s) whose state(s) you want to change.

Semantics

pdb_unplug_clause

This clause lets you unplug a PDB from a CDB. When you unplug a PDB, Oracle stores metadata for the PDB in an XML file. You can use this XML file to subsequently plug the PDB into a CDB.

  • For pdb_name, specify the name of the PDB you want to unplug. The PDB must be closed—that is, the open mode must be MOUNTED. In an Oracle Real Application Clusters (Oracle RAC) environment, the PDB must be closed in all Oracle RAC instances.

  • For filename, specify the full path name of the XML file in which to store the metadata for the unplugged PDB.

After a PDB is unplugged, it remains in the CDB with an open mode of MOUNTED and a status of UNPLUGGED. The only operation you can perform on an unplugged PDB is DROP PLUGGABLE DATABASE, which will remove it from the CDB. You must drop the PDB before you can plug it into the same CDB or another CDB.

See Also:

pdb_settings_clauses

These clauses lets you modify various settings for a PDB.

pdb_name

You can optionally use pdb_name to specify the name of the PDB whose settings you want to modify.

DEFAULT EDITION Clause

Use this clause to designate the specified edition as the default edition for the PDB. For the full semantics of this clause, refer to "DEFAULT EDITION Clause" in the ALTER DATABASE documentation.

SET DEFAULT TABLESPACE Clause

Use this clause to specify or change the default type of subsequently created tablespaces of the PDB. For the full semantics of this clause, refer to "SET DEFAULT TABLESPACE Clause" in the ALTER DATABASE documentation.

DEFAULT TABLESPACE Clause

Use this clause to establish or change the default permanent tablespace of the PDB. For the full semantics of this clause, refer to "DEFAULT TABLESPACE Clause" in the ALTER DATABASE documentation.

DEFAULT TEMPORARY TABLESPACE Clause

Use this clause to change the default temporary tablespace of the PDB to a new tablespace or tablespace group. For the full semantics of this clause, refer to "DEFAULT TEMPORARY TABLESPACE Clause" in the ALTER DATABASE documentation.

RENAME GLOBAL_NAME TO Clause

Use this clause to change the global name of the PDB. The new global name must be unique within the CDB. For an Oracle Real Application Clusters (Oracle RAC) database, the PDB must be open in READ WRITE RESTRICTED mode on the current instance only. The PDB must be closed on all other instances. For the full semantics of this clause, refer to "RENAME GLOBAL_NAME Clause" in the ALTER DATABASE documentation.

Note:

When you change the global name of a PDB, be sure to change the PLUGGABLE DATABASE property for database services that are used to connect to the PDB.

set_time_zone_clauses

Use this clause to modify the time zone setting for the PDB. For the full semantics of this clause, refer to set_time_zone_clause in the ALTER DATABASE documentation.

database_file_clauses

Use this clause to modify data files and temp files for the PDB. For the full semantics of this clause, refer to database_file_clauses in the ALTER DATABASE documentation.

supplemental_db_logging

Use this clause to instruct Oracle Database to add or stop adding supplemental data into the log stream for the PDB. This clause has the side effect of instructing the database to add or stop adding minimal supplemental data into the log stream for the entire CDB. For the full semantics of this clause, refer to supplemental_db_logging in the ALTER DATABASE documentation.

pdb_storage_clause

Use this clause to modify the storage limits for a PDB.

This clause has the same semantics as the pdb_storage_clause in the CREATE PLUGGABLE DATABASE documentation, with the following additions:

  • If you specify MAXSIZE size_clause, then the value you specify for size_clause must be greater than or equal to the combined size of the existing tablespaces belonging to the PDB. Otherwise, an error occurs.

  • If you specify MAX_SHARED_TEMP_SIZE size_clause, and the value you specify for size_clause is less than that used by sessions that are connected to the PDB, then no additional storage in the shared temporary tablespace will be available for sessions connected to the PDB until the amount of storage used by them becomes smaller than the value you specify for size_clause.

pdb_datafile_clause

This clause lets you bring data files associated with a PDB online or take them offline. The PDB must be closed when you issue this clause.

  • For pdb_name, specify the name of the PDB. If the current container is the PDB, then you can omit pdb_name.

  • The DATAFILE clauses let you specify the data files you want to bring online or take offline. Use filename or filenumber to identify specific data files by name or by number. You can view data file names and numbers by querying the NAME and FILE# columns of the V$DATAFILE dynamic performance view. Use ALL to specify all datafiles associated with the PDB.

  • Specify ONLINE to bring the data files online or OFFLINE to take the data files offline.

pdb_recovery_clauses

Use the pdb_recovery_clauses to back up and recover a PDB. You can optionally use pdb_name to specify the name of the PDB.

These clauses have the same semantics for ALTER PLUGGABLE DATABASE as they have for ALTER DATABASE. Refer to the recovery_clauses of ALTER DATABASE for the full semantics of these clauses.

pdb_change_state

This clause enables you to change the state, or open mode, of a PDB. Table 11-1 lists the open modes of a PDB.

  • Specify the pdb_open clause to change the open mode to READ WRITE, READ ONLY, or MIGRATE.

  • Specify the pdb_close clause to change the open mode to MOUNTED.

Table 11-1 PDB Open Modes

Open Mode Description

READ WRITE

A PDB in open read/write mode allows queries and user transactions to proceed and allows users to generate redo logs.

READ ONLY

A PDB in open read-only mode allows queries but does not allow user changes.

MIGRATE

When a PDB is in open migrate mode, you can run database upgrade scripts on the PDB.

MOUNTED

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.


You can view the open mode of a PDB by querying the OPEN_MODE column of the V$PDBS view.

See Also:

Oracle Database Administrator's Guide for a complete description of PDB open modes

pdb_name

You can optionally use pdb_name to specify the name of the PDB whose open mode you want to change.

pdb_open

This clause lets you change the open mode of a PDB to READ WRITE, READ ONLY, or MIGRATE. When you specify this clause, the PDB must be in MOUNTED mode unless you specify the FORCE keyword.

If you do not specify READ WRITE or READ ONLY, then the default is READ WRITE. The exception is when the PDB belongs to a CDB that is used as a physical standby database, in which case the default is READ ONLY.

READ WRITE Specify this clause to change the open mode to READ WRITE.

READ ONLY Specify this clause to change the open mode to READ ONLY.

[READ WRITE] UPGRADE Specify this clause to change the open mode to MIGRATE. The READ WRITE keywords are optional and are provided for semantic clarity.

RESTRICTED If you specify the optional RESTRICTED keyword, then the PDB is accessible only to users with the RESTRICTED SESSION privilege in the PDB.

If the PDB is in READ WRITE or READ ONLY mode, and you specify the RESTRICTED and FORCE keywords while changing the open mode, then all sessions connected to the PDB that do not have the RESTRICTED SESSION privilege in the PDB are terminated, and their transactions are rolled back.

FORCE Specify this keyword to change the open mode of a PDB from READ WRITE to READ ONLY, or from READ ONLY to READ WRITE. The FORCE keyword allows users to remain connected to the PDB while the open mode is changed.

When you specify FORCE to change the open mode of a PDB from READ WRITE to READ ONLY, any READ WRITE transaction that is open when you change the open mode will not be allowed to perform any more DML operations or to COMMIT.

Restriction on FORCE You cannot specify the FORCE keyword if the PDB is currently in MIGRATE mode, and you cannot specify the FORCE keyword to change a currently open PDB to MIGRATE mode.

RESETLOGS Specify this clause to create a new PDB incarnation and open the PDB in READ WRITE mode after point-in-time recovery of the PDB.

See Also:

Oracle Database Backup and Recovery User's Guide for more information on performing point-in-time recovery of CDBs and PDBs

instances_clause In an Oracle Real Application Clusters environment, use this clause to modify the state of the PDB in the specified Oracle RAC instances. If you omit this clause, then the state of the PDB is modified only in the current instance.

  • Use instance_name to specify one or more instance names in a comma-separated list enclosed in parenthesis to modify the state of the PDB in those instances.

  • Specify ALL to modify the state of the PDB in all instances.

  • Specify ALL EXCEPT to modify the state of the PDB in all instances except the specified instances.

If the PDB is already open in one or more instances, then you can open it in additional instances, but it must be opened in the same mode as in the instances in which it is already open.

pdb_close

This clause lets you change the open mode of a PDB to MOUNTED. When you specify this clause, the PDB must be in READ WRITE, READ ONLY, or MIGRATE mode. This clause is the PDB equivalent of the SQL*Plus SHUTDOWN command.

IMMEDIATE If you specify the optional IMMEDIATE keyword, then this clause is the PDB equivalent of the SQL*Plus SHUTDOWN command with the immediate mode. Otherwise, the PDB is shut down with the normal mode.

See Also:

SQL*Plus User's Guide and Reference for more information on the SQL*Plus SHUTDOWN command

instances_clause In an Oracle Real Application Clusters environment, use this clause to modify the state of the PDB in the specified Oracle RAC instances. You can close a PDB in some instances and leave it open in others. Refer to the instances_clause for the full semantics of this clause.

relocate_clause In an Oracle Real Application Clusters environment, use this clause to instruct the database to reopen the PDB on a different Oracle RAC instance.

  • Specify RELOCATE to reopen the PDB on a different instance that is selected by Oracle Database.

  • Specify RELOCATE TO 'instance_name' to reopen the PDB in the specified instance.

  • Specify NORELOCATE to close the PDB in the current instance. This is the default.

pdb_change_state_from_root

This clause enables you to modify the state of one or more PDBs.

  • Specify the pdb_name for one or more PDBs whose state you want to modify.

  • Specify ALL to modify the state of all PDBs in the CDB.

  • Specify ALL EXCEPT to modify the state of all PDBs in the CDB except those specified by using pdb_name.

If a PDB is already in the specified state, then the PDB's state is unchanged and no error is returned. If the state of a PDB cannot be changed, then an error occurs only for that PDB.

Refer to pdb_open and pdb_close for the full semantics of these clauses.

Examples

Unplugging a PDB from a CDB: Example The following statement unplugs PDB pdb1 and stores metadata for the PDB into XML file /oracle/data/pdb1.xml:

ALTER PLUGGABLE DATABASE pdb1
  UNPLUG INTO '/oracle/data/pdb1.xml';

Modifying the Settings of a PDB: Example The following statement changes the limit for the amount of storage used by all tablespaces in PDB pdb2 to 500M:

ALTER PLUGGABLE DATABASE pdb2
  STORAGE (MAXSIZE 500M);

Taking the Data Files of a PDB Offline: Example The following statement takes the data files associated with PDB pdb3 offline:

ALTER PLUGGABLE DATABASE pdb3
  DATAFILE ALL OFFLINE;

Changing the State of a PDB: Examples Assume that PDB pdb4 is closed—that it, its open mode is MOUNTED. The following statement opens pdb4 with open mode READ ONLY:

ALTER PLUGGABLE DATABASE pdb4
  OPEN READ ONLY;

The following statement uses the FORCE keyword to change the open mode of pdb4 from READ ONLY to READ WRITE:

ALTER PLUGGABLE DATABASE pdb4
  OPEN READ WRITE FORCE;

The following statement closes PDB pdb4:

ALTER PLUGGABLE DATABASE pdb4
  CLOSE;

The following statement opens PDB pdb4 with open mode READ ONLY. Because the RESTRICTED keyword is specified, the PDB is accessible only to users with the RESTRICTED SESSION privilege in the PDB.

ALTER PLUGGABLE DATABASE pdb4
  OPEN READ ONLY RESTRICTED;

Assume that PDB pdb5 is closed—that it, its open mode is MOUNTED. In an Oracle Real Application Clusters environment, the following statement opens PDB pdb5 with open mode READ WRITE in instances ORCLDB_1 and ORCLDB_2:

ALTER PLUGGABLE DATABASE pdb5
  OPEN READ WRITE INSTANCES = ('ORCLDB_1', 'ORCLDB_2');

In an Oracle Real Application Clusters environment, the following statement closes PDB pdb6 in the current instance and instructs the database to reopen pdb6 in instance ORCLDB_3:

ALTER PLUGGABLE DATABASE pdb6
  CLOSE RELOCATE TO 'ORCLDB_3';

Changing the State of All PDBs in a CDB: Example Assume that the current container is the root. The following statement opens all PDBs in the CDB with open mode READ ONLY:

ALTER PLUGGABLE DATABASE ALL
  OPEN READ ONLY;