Oracle Multitenant Environment (五) Create PDB

Creating and Removing PDBs with SQL*Plus

This chapter contains the following topics:

• About Creating and Removing PDBs

• Preparing for PDBs

• Creating a PDB Using the Seed

• Creating a PDB by Plugging an Unplugged PDB into a CDB

• Unplugging a PDB from a CDB

• Dropping a PDB

About Creating and Removing PDBs

You can create a pluggable database (PDB) in a multitenant container database (CDB) in the following ways:

• Create the new PDB by using the seed.

• Create the new PDB by cloning an existing PDB or non-CDB.

• Plug an unplugged PDB into a CDB.

• Create the new PDB by using a non-CDB.

You can remove a PDB from a CDB in the following ways:

• Unplug the PDB from a CDB.

• Drop the PDB.

This section contains the following topics:

• Techniques for Creating a PDB

• The CREATE PLUGGABLE DATABASE Statement

Techniques for Creating a PDB

This section describes the techniques that you can use to create a PDB. Creating a PDB is the process of associating the PDB with a CDB. You create a PDB when you want to use the PDB as part of the CDB.

Table 38-1 describes the techniques that you can use to create a PDB.

Table 38-1 Techniques for Creating a PDB

Technique	Description	More Information
Create a PDB by using the seed	Create a PDB in a CDB using the files of the seed. This technique copies the files associated with the seed to a new location and associates the copied files with the new PDB.	"Creating a PDB Using the Seed"
Create a PDB by cloning an existing PDB or non-CDB	Create a PDB by cloning a source PDB or non-CDB and plugging the clone into the CDB. A source can be a PDB in the local CDB, a PDB in a remote CDB, or a non-CDB. This technique copies the files associated with the source to a new location and associates the copied files with the new PDB.	"Creating a PDB by Cloning an Existing PDB or Non-CDB"
Create a PDB by plugging an unplugged PDB into a CDB	Create a PDB by using the XML metadata file that describes the PDB and the files associated with the PDB to plug it into the CDB.	"Creating a PDB by Plugging an Unplugged PDB into a CDB"
Create a PDB by using a non-CDB	Create a PDB by moving a non-CDB into a PDB. You can use the DBMS_PDBpackage to create an unplugged PDB from an Oracle Database 12c non-CDB. You can then plug the unplugged PDB into the CDB.	"Creating a PDB Using a Non-CDB"

All of the techniques described in Table 38-1 use the CREATE PLUGGABLE DATABASE statement to create a PDB. These techniques fall into two main categories: copying and plugging in. Figure 38-1 depicts the options for creating a PDB:

 

You can unplug a PDB when you want to plug it into a different CDB. You can unplug or drop a PDB when you no longer need it. An unplugged PDB is not usable until it is plugged into a CDB.

The CREATE PLUGGABLE DATABASE Statement

You use the CREATE PLUGGABLE DATABASE statement to create a PDB. All of the techniques described in Table 38-1, "Techniques for Creating a PDB" use this statement.

The following sections describe the clauses for the CREATE PLUGGABLE DATABASE statement and when to use each clause:

• Storage Limits

• File Location of the New PDB

• Restrictions on PDB File Locations

• Source File Locations When Plugging In an Unplugged PDB

• Temp File Reuse

• User Tablespaces

• PDB Tablespace Logging

• PDB Inclusion in Standby CDBs

• Excluding Data When Cloning a PDB

Storage Limits

The optional STORAGE clause of the CREATE PLUGGABLE DATABASE statement specifies the following limits:

• The amount of storage that can be used by all tablespaces that belong to the PDB

  Use MAXSIZE and a size clause to specify a limit, or set MAXSIZE to UNLIMITED to indicate no limit.

• The amount of storage in the default temporary tablespace shared by all PDBs that can be used by sessions connected to the PDB

  Use MAX_SHARED_TEMP_SIZE and a size clause to specify a limit, or set MAX_SHARED_TEMP_SIZE to UNLIMITED to indicate no limit.

If STORAGE UNLIMITED is set, or if there is no STORAGE clause, then there are no storage limits for the PDB.

The following are examples that use the STORAGE clause:

• Example 38-1, "STORAGE Clause That Specifies Storage Limits"

• Example 38-2, "STORAGE Clause That Specifies Storage Limits for the Shared Temporary Tablespace Only"

• Example 38-3, "STORAGE Clause That Specifies Unlimited Storage"

Example 38-1 STORAGE Clause That Specifies Storage Limits

This STORAGE clause specifies that the storage used by all tablespaces that belong to the PDB must not exceed 2 gigabytes. It also specifies that the storage used by the PDB sessions in the shared temporary tablespace must not exceed 100 megabytes.

STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)

Example 38-2 STORAGE Clause That Specifies Storage Limits for the Shared Temporary Tablespace Only

This STORAGE clause specifies unlimited storage for all tablespaces that belong to the PDB. It also specifies that the storage used by the PDB sessions in the shared temporary tablespace must not exceed 50 megabytes.

STORAGE (MAXSIZE UNLIMITED MAX_SHARED_TEMP_SIZE 50M)

Example 38-3 STORAGE Clause That Specifies Unlimited Storage

This STORAGE clause specifies that the PDB has unlimited storage for both its own tablespaces and the shared temporary tablespace.

STORAGE UNLIMITED

 

File Location of the New PDB

In this section, the term "file name" means both the name and the location of a file. The CREATE PLUGGABLE DATABASE statement has the following clauses that indicate the file names of the new PDB being created:

• The FILE_NAME_CONVERT clause specifies the names of the PDB's files after the PDB is created.

  Use this clause when the files are not yet at their ultimate destination, and you want to copy or move them during PDB creation. You can use this clause in any CREATE PLUGGABLE DATABASE statement.

• Starting with Oracle Database 12c Release 1 (12.1.0.2), the CREATE_FILE_DEST clause specifies the default Oracle Managed Files file system directory or Oracle ASM disk group for the PDB's files.

  Use this clause to enable Oracle Managed Files for the new PDB, independent of any Oracle Managed Files default location specified in the root for the CDB. You can use this clause in any CREATE PLUGGABLE DATABASE statement.

When necessary, you can use both of these clauses in the same CREATE PLUGGABLE DATABASE statement. In addition, the following initialization parameters can control the location of the new PDB's files:

• The DB_CREATE_FILE_DEST initialization parameter set in the root

  This initialization parameter specifies the default location for Oracle Managed Files for the CDB. When this parameter is set in a PDB, it specifies the default location for Oracle Managed Files for the PDB.

• The PDB_FILE_NAME_CONVERT initialization parameter(在创建CDB的时候，该初始化参数也可以用来指定seed 的file name)

  This initialization parameter maps names of existing files to new file names when processing a CREATE PLUGGABLE DATABASE statement.

When both clauses are used in the same CREATE PLUGGABLE DATABASE statement, and both initialization parameters are set, the precedence order is:

1. The FILE_NAME_CONVERT clause

2. The CREATE_FILE_DEST clause

3. The DB_CREATE_FILE_DEST initialization parameter set in the root

4. The PDB_FILE_NAME_CONVERT initialization parameter

If FILE_NAME_CONVERT and CREATE_FILE_DEST are both specified, then the FILE_NAME_CONVERT setting is used for the files being placed during PDB creation, and the CREATE_FILE_DEST setting is used to set the DB_CREATE_FILE_DEST initialization parameter in the PDB. In this case, Oracle Managed Files controls the location of the files for the PDB after PDB creation.

The following sections describe the PDB file location clauses in more detail:

• FILE_NAME_CONVERT Clause

• CREATE_FILE_DEST Clause

 

FILE_NAME_CONVERT Clause

If the PDB will not use Oracle Managed Files, then the FILE_NAME_CONVERT clause of the CREATE PLUGGABLE DATABASE statement specifies how to generate the names of files (such as data files) using the names of existing files.

You can use this clause to specify one of the following options:

• One or more file name patterns and replacement file name patterns, in the following form:

  'string1' , 'string2' , 'string3' , 'string4' , ...
  

  The string2 file name pattern replaces the string1 file name pattern, and the string4 file name pattern replaces the string3 file name pattern. You can use as many pairs of file name pattern and replacement file name pattern strings as required.

  If you specify an odd number of strings (the last string has no corresponding replacement string), then an error is returned. Do not specify more than one pattern/replace string that matches a single file name or directory.

• NONE when no files should be copied or moved during PDB creation. Omitting the FILE_NAME_CONVERT clause is the same as specifying NONE.

You can use the FILE_NAME_CONVERT clause in any CREATE PLUGGABLE DATABASE statement.

When the FILE_NAME_CONVERT clause is not specified in a CREATE PLUGGABLE DATABASE statement, either Oracle Managed Files or thePDB_FILE_NAME_CONVERT initialization parameter specifies how to generate the names of the files. If you use both Oracle Managed Files and thePDB_FILE_NAME_CONVERT initialization parameter, then Oracle Managed Files takes precedence. The FILE_NAME_CONVERT clause takes precedence when it is specified.

File name patterns specified in the FILE_NAME_CONVERT clause cannot match files or directories managed by Oracle Managed Files.

Example 38-4 FILE_NAME_CONVERT Clause

This FILE_NAME_CONVERT clause generates file names for the new PDB in the /oracle/pdb5 directory using file names in the /oracle/dbs directory.

FILE_NAME_CONVERT = ('/oracle/dbs/', '/oracle/pdb5/')

CREATE_FILE_DEST Clause

If the PDB will use Oracle Managed Files, then the CREATE_FILE_DEST clause of the CREATE PLUGGABLE DATABASE statement specifies the default file system directory or Oracle ASM disk group for the PDB's files.

If a file system directory is specified as the default location in this clause, then the directory must exist. Also, the user who runs the CREATE PLUGGABLEDATABASE statement must have the appropriate privileges to create files in the specified directory.

If there is a default Oracle Managed Files location for the CDB set in the root, then the CREATE_FILE_DEST setting overrides the default location for the CDB.

If CREATE_FILE_DEST=NONE is specified, then Oracle Managed Files is disabled for the PDB.

When the CREATE_FILE_DEST clause is set to a value other than NONE, the DB_CREATE_FILE_DEST initialization parameter is set implicitly in the PDB withSCOPE=SPFILE.

If the root uses Oracle Managed Files, and this clause is not specified, then the PDB inherits the Oracle Managed Files default location from the root.

Example 38-5 CREATE_FILE_DEST Clause

This CREATE_FILE_DEST clause specifies /oracle/pdb2/ as the default Oracle Managed Files file system directory for the new PDB.

CREATE_FILE_DEST = '/oracle/pdb2/'

Restrictions on PDB File Locations

The PATH_PREFIX clause of the CREATE PLUGGABLE DATABASE statement ensures that all relative directory object paths associated with the PDB are restricted to the specified directory or its subdirectories. Use this clause when you want to ensure that a PDB's files reside in a specific directory and its subdirectories when relative paths are used for directory objects.

You can use this clause to specify one of the following options:

• An absolute path that is used as a prefix for all relative directory object paths associated with the PDB.

• NONE to indicate that paths associated with directory objects are treated as absolute paths. Omitting the PATH_PREFIX clause is the same as specifyingNONE.

After a PDB is created, its PATH_PREFIX setting cannot be modified.

You can use the PATH_PREFIX clause in any CREATE PLUGGABLE DATABASE statement.

The PATH_PREFIX clause is ignored when absolute paths are used for directory objects.

The PATH_PREFIX clause does not affect files created by Oracle Managed Files.

Example 38-6 PATH_PREFIX Clause

This PATH_PREFIX clause ensures that all relative directory object paths associated with the PDB are relative to the /disk1/oracle/dbs/salespdb directory.

PATH_PREFIX = '/disk1/oracle/dbs/salespdb/'

Be sure to specify the path name so that it is properly formed when file names are appended to it. For example, on UNIX systems, be sure to end the path name with a forward slash (/).

Source File Locations When Plugging In an Unplugged PDB

When you plug an unplugged PDB into a CDB, the CREATE PLUGGABLE DATABASE ... USING statement must be able to identify the PDB's files. An XML file describes the names of an unplugged PDB's source files. The XML file might not describe the names of these files accurately if you transported the unplugged files from one storage system to a different one. The files are in a new location, but the file paths in the XML file still indicate the old location. In this case, use this clause to specify the accurate names of the files. Use this clause only when you are plugging in an unplugged PDB with a CREATE PLUGGABLE DATABASE... USING statement.

The SOURCE_FILE_NAME_CONVERT clause of the CREATE PLUGGABLE DATABASE ... USING statement specifies how to locate files (such as data files) listed in an XML file describing a PDB if they reside in a location different from that specified in the XML file.

You can use this clause to specify one of the following options:

• One or more file name patterns and replacement file name patterns, in the following form:

  'string1' , 'string2' , 'string3' , 'string4' , ...
  

  The string2 file name pattern replaces the string1 file name pattern, and the string4 file name pattern replaces the string3 file name pattern. You can use as many pairs of file name pattern and replacement file name pattern strings as required.

  When you use this clause, ensure that the files you want to use for the PDB reside in the replacement file name patterns. Move or copy the files to these locations if necessary.

• NONE when no file names need to be located because the PDB's XML file describes the file names accurately. Omitting the SOURCE_FILE_NAME_CONVERTclause is the same as specifying NONE.

You can use the SOURCE_FILE_NAME_CONVERT clause only in a CREATE PLUGGABLE DATABASE statement with a USING clause. Therefore, you can use this clause only when you are plugging in an unplugged PDB.

Example 38-7 SOURCE_FILE_NAME_CONVERT Clause

This SOURCE_FILE_NAME_CONVERT clause uses the files in the /disk2/oracle/pdb7 directory instead of the /disk1/oracle/pdb7 directory. In this case, the XML file describing a PDB specifies the /disk1/oracle/pdb7 directory, but the PDB should use the files in the /disk2/oracle/pdb7 directory.

SOURCE_FILE_NAME_CONVERT = ('/disk1/oracle/pdb7/', '/disk2/oracle/pdb7/')

 

Temp File Reuse

The TEMPFILE REUSE clause of the CREATE PLUGGABLE DATABASE statement specifies that an existing temp file in the target location is reused. When you specify this clause, Oracle Database formats the temp file and reuses it. The previous contents of the file are lost. If this clause is specified, and there is no temp file in the target location, then Oracle Database creates a new temp file for the PDB.

If you do not specify this clause, and the new PDB will not use the CDB's default temporary tablespace, then the CREATE PLUGGABLE DATABASE statement creates a new temp file for the PDB. If a file exists with the same name as the new temp file in the target location, then an error is returned, and the PDB is not created. Therefore, if you do not specify the TEMPFILE REUSE clause, then ensure that such a temp file does not exist in the target location.

User Tablespaces

The USER_TABLESPACES clause of the CREATE PLUGGABLE DATABASE statement specifies which tablespaces are available in the new PDB.

You can use this clause to separate the data for multiple schemas into different PDBs. For example, when you move a non-CDB to a PDB, and the non-CDB had a number of schemas that each supported a different application, you can use this clause to separate the data belonging to each schema into a separate PDB, assuming that each schema used a separate tablespace in the non-CDB.

You can use this clause to specify one of the following options:

• List one or more tablespaces to include.

• Specify ALL, the default, to include all of the tablespaces.

• Specify ALL EXCEPT to include all of the tablespaces, except for the tablespaces listed.

• Specify NONE to exclude all of the tablespaces.

The tablespaces that are excluded by this clause are offline in the new PDB, and all data files that belong to these tablespaces are unnamed and offline.

This clause does not apply to the SYSTEM, SYSAUX, or TEMP tablespaces. Do not include these tablespaces in a tablespace list for this clause.

The following are examples that use the USER_TABLESPACES clause:

• USER_TABLESPACES Clause That Includes One Tablespace

• USER_TABLESPACES Clause That Includes a List of Tablespaces

• USER_TABLESPACES Clause That Includes All Tablespaces Except for Listed Ones

Example 38-9 USER_TABLESPACES Clause That Includes One Tablespace

Assume that the non-CDB or PDB from which a PDB is being created includes the following tablespaces: tbs1, tbs2, and tbs3. This USER_TABLESPACES clause includes the tbs2 tablespace, but excludes the tbs1 and tbs3 tablespaces.

USER_TABLESPACES=('tbs2');

Example 38-10 USER_TABLESPACES Clause That Includes a List of Tablespaces

Assume that the non-CDB or PDB from which a PDB is being created includes the following tablespaces: tbs1, tbs2, tbs3, tbs4, and tbs5. ThisUSER_TABLESPACES clause includes the tbs1, tbs4, and tbs5 tablespaces, but excludes the tbs2 and tbs3 tablespaces.

USER_TABLESPACES=('tbs1','tbs4','tbs5');

Example 38-11 USER_TABLESPACES Clause That Includes All Tablespaces Except for Listed Ones

Assume that the non-CDB or PDB from which a PDB is being created includes the following tablespaces: tbs1, tbs2, tbs3, tbs4, and tbs5. ThisUSER_TABLESPACES clause includes the tbs2 and tbs3 tablespaces, but excludes the tbs1, tbs4, and tbs5 tablespaces.

USER_TABLESPACES=ALL EXCEPT('tbs1','tbs4','tbs5');

 

 

PDB Tablespace Logging

The logging_clause of the CREATE PLUGGABLE DATABASE statement specifies the logging attribute of the PDB. The logging attribute controls whether certain DML operations are logged in the redo log file (LOGGING) or not (NOLOGGING).

You can use this clause to specify one of the following attributes:

• LOGGING, the default, indicates that any future tablespaces created within the PDB will be created with the LOGGING attribute by default.

• NOLOGGING indicates that any future tablespaces created within the PDB will be created with the NOLOGGING attribute by default.

You can override the default logging attribute by specifying either LOGGING or NOLOGGING at the schema object level--for example, in a CREATE TABLEstatement.

The specified attribute is used to establish the logging attribute of tablespaces created within the PDB if the logging_clause is not specified in the CREATE TABLESPACE statement.

The DBA_PDBS view shows the current logging attribute for a PDB.

Example 38-12 Specifying the LOGGING Attribute for the PDB

LOGGING

Example 38-13 Specifying the NOLOGGING Attribute for the PDB

NOLOGGING

See Also:

• "Controlling the Writing of Redo Records"

• Oracle Database SQL Language Reference for more information about the logging attribute

PDB Inclusion in Standby CDBs

The STANDBYS clause of the CREATE PLUGGABLE DATABASE statement specifies whether the new PDB is included in standby CDBs. You can specify one of the following values for the STANDBYS clause:

• ALL includes the new PDB in all of the standby CDBs.

• NONE excludes the new PDB from all of the standby CDBs.

When a PDB is not included in any of the standby CDBs, the PDB's data files are offline and marked as unnamed on all of the standby CDBs. Any new standby CDBs that are instantiated after the PDB has been created must disable the PDB for recovery explicitly to exclude it from the standby CDB. It is possible to enable a PDB on a standby CDB after it was excluded on that standby CDB.

Example 38-14 STANDBYS Clause That Includes the New PDB on All Standby CDBs

STANDBYS=ALL

Example 38-15 STANDBYS Clause That Excludes the New PDB from All Standby CDBs

STANDBYS=NONE

See Also:

Oracle Data Guard Concepts and Administration for more information about configuring PDBs on standby CDBs

Excluding Data When Cloning a PDB

The NO DATA clause of the CREATE PLUGGABLE DATABASE statement specifies that a PDB's data model definition is cloned but not the PDB's data. The dictionary data in the source PDB is cloned, but all user-created table and index data from the source PDB is discarded. This clause is useful for quickly creating clones of a PDB with only the object definitions and no data. Use this clause only when you are cloning a PDB with a CREATE PLUGGABLE DATABASE ... FROMstatement.

This clause does not apply to the SYSTEM and SYSAUX tablespaces. If user-created database objects in the source PDB are stored in one of these tablespaces, the database objects will contain data in the cloned PDB.

When the NO DATA clause is included in the CREATE PLUGGABLE DATABASE statement, the source PDB cannot contain the following types of tables:

• Index-organized tables

• Advanced Queue (AQ) tables

• Clustered tables

• Table clusters

Example 38-16 NO DATA Clause

NO DATA

See Also:

Oracle Database SQL Language Reference

Preparing for PDBs

Ensure that the following prerequisites are met before creating a PDB:

• The CDB must exist.

  See Chapter 37, "Creating and Configuring a CDB".

• The CDB must be in read/write mode.

• The current user must be a common user whose current container is the root.

• The current user must have the CREATE PLUGGABLE DATABASE system privilege.

• You must decide on a unique PDB name for each PDB. Each PDB name must be unique in a single CDB, and each PDB name must be unique within the scope of all the CDBs whose instances are reached through a specific listener.

  The PDB name is used to distinguish a PDB from other PDBs in the CDB. PDB names follow the same rules as service names, which includes being case-insensitive. See Oracle Database Net Services Reference for information about the rules for service names.

• If you are creating a PDB in an Oracle Data Guard configuration with a physical standby database, then additional tasks must be completed before creating a PDB. See Oracle Data Guard Concepts and Administration for more information.

See Also:

"About the Current Container"

Creating a PDB Using the Seed

You can use the CREATE PLUGGABLE DATABASE statement to create a PDB in a CDB using the files of the seed. This section describes using this statement to create a new PDB.

This section contains the following topics:

• About Creating a PDB from the Seed

• Creating a PDB from the Seed

 

About Creating a PDB from the Seed

You can use the CREATE PLUGGABLE DATABASE statement to create a new PDB by using the files of the seed. The statement copies these files to a new location and associates them with the new PDB. Figure 38-2 illustrates how this technique creates a new PDB.

 

When you create a new PDB from the seed, you must specify an administrator for the PDB in the CREATE PLUGGABLE DATABASE statement. The statement creates the administrator as a local user in the PDB and grants the PDB_DBA role locally to the administrator.

When you create a PDB using the seed, you must address the questions in Table 38-2. The table describes which CREATE PLUGGABLE DATABASE clauses you must specify based on different factors.

Table 38-2 Clauses for Creating a PDB From the Seed

Clause	Question	Yes	No
STORAGE	Do you want to limit the amount of storage that the PDB can use?	Specify a STORAGE clause with the appropriate limits.	Omit the STORAGE clause, or specify unlimited storage using the STORAGEclause.
DEFAULT TABLESPACE	Do you want to specify a default permanent tablespace for the PDB?	Specify a DEFAULT TABLESPACE clause with the appropriate limits. Oracle Database creates a smallfile tablespace and subsequently will assign to this tablespace any non-SYSTEMusers for whom you do not specify a different permanent tablespace.	Omit the DEFAULT TABLESPACE clause. If you do not specify this clause, then theSYSTEM tablespace is the default permanent tablespace for non-SYSTEMusers. Using the SYSTEM tablespace for non-SYSTEM users is not recommended.
PATH_PREFIX	Do you want to use a PATH_PREFIXclause to ensure that all relative directory object paths associated with the PDB are treated as relative to the specified directory or its subdirectories? The PATH_PREFIX clause is ignored when absolute paths are used for directory objects. The PATH_PREFIX clause does not affect files created by Oracle Managed Files.	Include a PATH_PREFIX clause that specifies an absolute path.	Set the PATH_PREFIX clause to NONE or omit it.
FILE_NAME_CONVERT	Do you want to use aFILE_NAME_CONVERT clause to specify the target locations of the files? The source files are the files associated with the seed.	Include a FILE_NAME_CONVERT clause that specifies the target locations of the files based on the names of the source files.	Omit the FILE_NAME_CONVERT clause. Use one of these techniques to specify the target locations of the files: CREATE_FILE_DEST clause Enable Oracle Managed Files for the CDB for it to determine the target locations. Specify the target locations in thePDB_FILE_NAME_CONVERTinitialization parameter. See "File Location of the New PDB".
CREATE_FILE_DEST	Do you want to use aCREATE_FILE_DEST clause to specify the Oracle Managed Files default location for the PDB's files? The source files are the files associated with the seed.	Include a CREATE_FILE_DEST clause that specifies the default file system directory or Oracle ASM disk group for the PDB's files.	Omit the CREATE_FILE_DEST clause. Use one of these techniques to specify the target locations of the files: FILE_NAME_CONVERT clause Enable Oracle Managed Files for the CDB for it to determine the target locations. Specify the target locations in thePDB_FILE_NAME_CONVERTinitialization parameter. See "File Location of the New PDB".
TEMPFILE REUSE	Do you want to reuse the temp file if a temp file exists in the target location?	Include the TEMPFILE REUSE clause.	Omit the TEMPFILE REUSE clause. Ensure that there is no file with the same name as the new temp file in the target location.
USER_TABLESPACES	Do you want to specify which tablespaces are included in the new PDB and which tablespaces are excluded from the new PDB? This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).	Include the USER_TABLESPACES clause and specify the tablespaces that are included in the new PDB.	Omit the USER_TABLESPACES clause.
logging_clause	Do you want to specify the logging attribute of the tablespaces in the new PDB? This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).	Include the logging_clause.	Omit the logging_clause.
ROLES	Do you want to grant predefined Oracle roles to the PDB_DBA role locally in the PDB? The new administrator for the PDB is granted the PDB_DBA common role locally in the PDB. By default, theCREATE PLUGGABLE DATABASE statement does not grant the administrator or the role any privileges.	Include the ROLES clause and specify the predefined Oracle roles to grant to the PDB_DBA role. The specified roles are granted to the PDB_DBA role locally in the PDB. The user who runs theCREATE PLUGGABLE DATABASE statement does not need to be granted the specified roles. See Oracle Database Security Guide for information about predefined Oracle roles.	Omit the ROLES clause.

The ROLES clause can be used only when you are creating a PDB from the seed, but the other clauses described in Table 38-2 are general clauses. See "The CREATE PLUGGABLE DATABASE Statement" for more information about the general clauses.

Creating a PDB from the Seed

You can create a PDB from the seed using the CREATE PLUGGABLE DATABASE statement.

To create a PDB from the seed: 

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

2. Run the CREATEPLUGGABLEDATABASE statement, and specify a local administrator for the PDB. Specify other clauses when they are required.  

   After you create the PDB, it is in mounted mode, and its status is NEW. You can view the open mode of a PDB by querying the OPEN_MODE column in theV$PDBS view. You can view the status of a PDB by querying the STATUS column of the CDB_PDBS or DBA_PDBS view.

   A new default service is created for the PDB. The service has the same name as the PDB and can be used to access the PDB. 

3. Open the new PDB in read/write mode.   You must open the new PDB in read/write mode for Oracle Database to complete the integration of the new PDB into the CDB. An error is returned if you attempt to open the PDB in read-only mode. After the PDB is opened in read/write mode, its status is NORMAL.
4. Back up the PDB.
5. A local user with the name of the specified local administrator is created and granted the PDB_DBA common role locally in the PDB. If this user was not granted administrator privileges during PDB creation, then use the SYS and SYSTEM common users to administer to the PDB.

Example 38-17 Creating a PDB Using No Clauses

This example assumes the following factors:

• Storage limits are not required for the PDB. Therefore, the STORAGE clause is not required.

• The PDB does not require a default tablespace.

• The PATH_PREFIX clause is not required.

• The FILE_NAME_CONVERT clause and the CREATE_FILE_DEST clause are not required.

  Either Oracle Managed Files is enabled for the CDB, or the PDB_FILE_NAME_CONVERT initialization parameter is set. The files associated with the seed will be copied to a new location based on the Oracle Managed Files configuration or the initialization parameter setting.

• There is no file with the same name as the new temp file that will be created in the target location. Therefore, the TEMPFILE REUSE clause is not required.

• No predefined Oracle roles need to be granted to the PDB_DBA role.

Given the preceding factors, the following statement creates the PDB:

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password;

Example 38-18 Creating a PDB and Granting Predefined Oracle Roles to the PDB Administrator

This example assumes the following factors:

• Storage limits are not required for the PDB. Therefore, the STORAGE clause is not required.

• The PDB does not require a default tablespace.

• The PATH_PREFIX clause is not required.

• The FILE_NAME_CONVERT clause and the CREATE_FILE_DEST clause are not required.

  Either Oracle Managed Files is enabled, or the PDB_FILE_NAME_CONVERT initialization parameter is set. The files associated with the seed will be copied to a new location based on the Oracle Managed Files configuration or the initialization parameter setting.

• There is no file with the same name as the new temp file that will be created in the target location. Therefore, the TEMPFILE REUSE clause is not required.

• The PDB_DBA role should be granted the following predefined Oracle role locally: DBA.

Given the preceding factors, the following statement creates the PDB:

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password
  ROLES=(DBA);

In addition to creating the salespdb PDB, this statement grants the PDB_DBA role to the PDB administrator salesadm and grants the specified predefined Oracle roles to the PDB_DBA role locally in the PDB.

Example 38-19 Creating a PDB Using the STORAGE, DEFAULT TABLESPACE, PATH_PREFIX, and FILE_NAME_CONVERT Clauses

This example assumes the following factors:

• Storage limits must be enforced for the PDB. Therefore, the STORAGE clause is required. Specifically, all tablespaces that belong to the PDB must not exceed 2 gigabytes, and the storage used by the PDB sessions in the shared temporary tablespace must not exceed 100 megabytes.

• A default permanent tablespace is required for any non-administrative users for which you do not specify a different permanent tablespace. Specifically, this example creates a default permanent tablespace named sales with the following characteristics:

  • The single data file for the tablespace is sales01.dbf, and the statement creates it in the /disk1/oracle/dbs/salespdb directory.

  • The SIZE clause specifies that the initial size of the tablespace is 250 megabytes.

  • The AUTOEXTEND clause enables automatic extension for the file.

• The PDB's relative directory object paths must be treated as relative to a specific directory. Therefore, the PATH_PREFIX clause is required. In this example, the PDB's relative directory object paths must be treated as relative to the /disk1/oracle/dbs/salespdb directory.

• The CREATE_FILE_DEST clause will not be used, Oracle Managed Files is not enabled, and the PDB_FILE_NAME_CONVERT initialization parameter is not set. Therefore, the FILE_NAME_CONVERT clause is required. Specify the location of the data files for the seed on your system. In this example, Oracle Database copies the files from /disk1/oracle/dbs/pdbseed to /disk1/oracle/dbs/salespdb.

  To view the location of the data files for the seed, run the query in Example 43-7, "Showing the Data Files for Each PDB in a CDB".

• There is no file with the same name as the new temp file that will be created in the target location. Therefore, the TEMPFILE REUSE clause is not required.

• No predefined Oracle roles need to be granted to the PDB_DBA role.

Given the preceding factors, the following statement creates the PDB:

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password
  STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
  DEFAULT TABLESPACE sales 
    DATAFILE '/disk1/oracle/dbs/salespdb/sales01.dbf' SIZE 250M AUTOEXTEND ON
  PATH_PREFIX = '/disk1/oracle/dbs/salespdb/'
  FILE_NAME_CONVERT = ('/disk1/oracle/dbs/pdbseed/', '/disk1/oracle/dbs/salespdb/');

See Also:

• Oracle Database SQL Language Reference for more information about the DEFAULT TABLESPACE clause

• Oracle Database Security Guide for guidelines about choosing passwords

 

Unplugging a PDB from a CDB

This section contains the following topics:

• About Unplugging a PDB

• Unplugging a PDB

About Unplugging a PDB

Unplugging a PDB disassociates the PDB from a CDB. You unplug a PDB when you want to move the PDB to a different CDB or when you no longer want the PDB to be available.

To unplug a PDB, connect to the root and use the ALTER PLUGGABLE DATABASE statement to specify an XML file that will contain metadata about the PDB after it is unplugged. The SQL statement creates the XML file, and it contains the required information to enable a CREATE PLUGGABLE DATABASE statement on a target CDB to plug in the PDB.

The PDB must be closed before it can be unplugged. When you unplug a PDB from a CDB, the unplugged PDB is in mounted mode. The unplug operation makes some changes in the PDB's data files to record, for example, that the PDB was successfully unplugged. Because it is still part of the CDB, the unplugged PDB is included in an RMAN backup of the entire CDB. Such a backup provides a convenient way to archive the unplugged PDB in case it is needed in the future.

To completely remove the PDB from the CDB, you can drop the PDB. The only operation supported on an unplugged PDB is dropping the PDB. The PDB must be dropped from the CDB before it can be plugged back into the same CDB. A PDB is usable only when it is plugged into a CDB.

Unplugging a PDB

The following prerequisites must be met:

• The current user must have SYSDBA or SYSOPER 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 or AS SYSOPER at connect time.

• The PDB must have been opened at least once.

• The PDB must be closed. In an Oracle Real Application Clusters (Oracle RAC) environment, the PDB must be closed on all instances.

Note:

If you are unplugging in a PDB that includes data that was encrypted with Transparent Data Encryption, then follow the instructions in Oracle Database Advanced Security Guide.

To unplug a PDB: 

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

   See "About the Current Container" and "Accessing a Container in a CDB with SQL*Plus".

2. Run the ALTER PLUGGABLE DATABASE statement with the UNPLUG INTO clause, and specify the PDB to unplug and the name and location of the PDB's XML metadata file.

Example 38-31 Unplugging PDB salespdb

This ALTER PLUGGABLE DATABASE statement unplugs the PDB salespdb and creates the salespdb.xml metadata file in the /oracle/data/ directory:

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