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

CREATE PLUGGABLE DATABASE

Purpose

Use the CREATE PLUGGABLE DATABASE statement to create a pluggable database (PDB).

This statement enables you to perform the following tasks:

  • Create a PDB by using the seed as a template

    Use the create_pdb_from_seed clause to create a PDB by using the seed in the multitenant container database (CDB) as a template. The files associated with the seed are copied to a new location and the copied files are then associated with the new PDB.

  • Create a PDB by cloning an existing PDB

    Use the create_pdb_clone clause to create a PDB by copying an existing PDB (the source PDB) and then plugging the copy into the CDB. The files associated with the source PDB are copied to a new location and the copied files are associated with the new PDB. This operation is called cloning a PDB.

    The source PDB can be plugged in or unplugged. If plugged in, then the source PDB can be in the same CDB or in a remote CDB. If the source PDB is in a remote CDB, then a database link is used to connect to the remote CDB and copy the files.

  • Create a PDB by plugging an unplugged PDB or a non-CDB into a CDB

    Use the create_pdb_from_xml clause to plug an unplugged PDB or a non-CDB into a CDB, using an XML metadata file.

Note:

When a new PDB is established in a CDB, it is possible that the name of a service offered by the new PDB will collide with an existing service name. The namespace in which a collision can occur is that of the listener that gives access to the CDB. Within that namespace, collisions are possible among the names of non-CDB's default services, CDB's default services, PDB's default services, and user-defined services. For example, if two or more CDBs on the same computer system use the same listener, and the newly established PDB has the same service name as another PDB in these CDBs, then a collision occurs.

You must not attempt to operate a PDB that causes a collision with an existing service name. If the colliding name is that of the PDB's default service, then you must rename the PDB. If the colliding name is that of a user-created service within the PDB, then you must drop that service and create one in its place, with a non-colliding name, that has the same purpose and properties.

See Also:

Prerequisites

You must be connected to a CDB and the current container must be the root.

You must have the CREATE PLUGGABLE DATABASE system privilege.

The CDB in which the PDB is being created must be in READ WRITE mode.

To specify the create_pdb_clone clause:

  • If src_pdb_name refers to a PDB in the same CDB, then you must have the CREATE PLUGGABLE DATABASE system privilege in the root of the CDB in which the new PDB will be created and in the PDB being cloned.

  • If src_pdb_name refers to a PDB in a remote database, then you must have the CREATE PLUGGABLE DATABASE system privilege in the root of the CDB in which the new PDB will be created and the remote user must have the CREATE PLUGGABLE DATABASE system privilege in the PDB to which src_pdb_name refers.

Semantics

pdb_name

Specify the name of the PDB to be created. The first character of a PDB name must be alphanumeric and the remaining characters can be alphanumeric or the underscore character (_).

The PDB name must be unique in the CDB, and it must be unique within the scope of all the CDBs whose instances are reached through a specific listener.

create_pdb_from_seed

This clause enables you to create a PDB by using the seed in the CDB as a template.

See Also:

Oracle Database Administrator's Guide for the complete steps for creating a PDB using the seed

ADMIN USER Clause

Use this clause to create an administrative user who can be granted the privileges required to perform administrative tasks on the PDB. For admin_user_name, specify name of the user to be created. Use the IDENTIFIED BY clause to specify the password for admin_user_name. Oracle Database creates a local user in the PDB and grants the PDB_DBA local role to that user.

pdb_dba_roles

This clause lets you grant one or more roles to the PDB_DBA role. Use this clause to grant roles that have the privileges required by the administrative user of the PDB. For role, specify a predefined role. For a list of predefined roles, refer to Oracle Database Security Guide.

You can also use the GRANT statement to grant roles to the PDB_DBA role after the PDB has been created. Until you have granted the appropriate privileges to the PDB_DBA role, the SYS and SYSTEM users can perform administrative tasks on a PDB.

default_tablespace

Specify this clause to create a default permanent tablespace for the PDB. The default_tablespace clause has the same semantics that it has for the CREATE DATABASE statement. For full information, refer to default_tablespace in the documentation for CREATE DATABASE.

file_name_convert

Use this clause to determine how the database generates the names of files (such as data files and wallet files) for the PDB.

  • For filename_pattern, specify a string found in names of files associated with the seed (when creating a PDB by using the seed), associated with the source PDB (when cloning a PDB), or listed in the XML file (when plugging a PDB into a CDB).

  • For replacement_filename_pattern, specify a replacement string.

Oracle Database will replace filename_pattern with replacement_filename_pattern when generating the names of files associated with the new PDB.

File name patterns cannot match files or directories managed by Oracle Managed Files.

You can specify FILE_NAME_CONVERT = NONE, which is the same as omitting this clause. If you omit this clause, then the database first attempts to use Oracle Managed Files to generate file names. If you are not using Oracle Managed Files, then the database uses the PDB_FILE_NAME_CONVERT initialization parameter to generate file names. If this parameter is not set, then an error occurs.

pdb_storage_clause

Use this clause to specify storage limits for the PDB.

  • Use MAXSIZE to limit the amount of storage that can be used by all tablespaces in the PDB to the value specified with size_clause. This limit includes the size of data files and temporary files for tablespaces belonging to the PDB. Specify MAXSIZE UNLIMITED to enforce no limit.

  • Use MAX_SHARED_TEMP_SIZE to limit the amount of storage in the shared temporary tablespace that can be used by sessions connected to the PDB to the value specified with size_clause. Specify MAX_SHARED_TEMP_SIZE UNLIMITED to enforce no limit.

If you omit this clause, or specify STORAGE UNLIMITED, then there are no storage limits for the PDB. This is equivalent to specifying STORAGE (MAXSIZE UNLIMITED MAX_SHARED_TEMP_SIZE UNLIMITED).

path_prefix_clause

Use this clause to ensure that file paths for directory objects and parameters associated with the PDB are restricted to the specified directory or its subdirectories. You cannot modify the setting of this clause after you create the PDB. This clause does not affect files created by Oracle Managed Files.

  • For path_name, specify the absolute path name of an operating system directory. The single quotation marks are required, with the result that the path name is case sensitive. Oracle Database uses path_name as a prefix to the relative paths for directory objects and certain parameters associated with the PDB. If a directory object or parameter has an absolute path, then the path_prefix_clause is ignored.

    Be sure to specify path_name so that the resulting path name will be properly formed when relative paths are appended to it. For example, on UNIX systems, be sure to end path_name with a forward slash (/), such as:

    PATH_PREFIX = '/disk1/oracle/dba/salespdb/'
    
  • If you specify PATH_PREFIX = NONE, then the relative paths for directory objects and certain parameters associated with the PDB are treated as absolute paths and are not restricted to a particular directory.

Omitting the path_prefix_clause is equivalent to specifying PATH_PREFIX = NONE.

tempfile_reuse_clause

When you create a PDB, Oracle Database associates temp files with the new PDB. Depending on how you create the PDB, the temp files may already exist and may have been previously used.

Specify TEMPFILE REUSE to instruct the database to format and reuse a temp file associated with the new PDB if it already exists. If you specify this clause and a temp file does not exist, then the database creates the temp file.

If you do not specify TEMPFILE REUSE and a temp file to be associated with the new PDB already exists, then the database returns an error and does not create the PDB.

create_pdb_clone

This clause enables you to create a new PDB by cloning an existing PDB (the source PDB). The source PDB can be in the local CDB or in a remote CDB. If it is in a remote CDB, then the local CDB and the remote CDB must meet the following requirements:

  • They must have the same endian format.

  • They must have compatible character sets and national character sets, which means:

    • Every character in the remote CDB character set is available in the local CDB character set.

    • Every character in the remote CDB character set has the same code point value in the local CDB character set.

  • They must have the same set of database options installed.

Users in the source PDB who use the CDB default temporary tablespace are assigned the default temporary tablespace of the CDB that contains the new PDB. Users who use the default temporary tablespace local to the PDB continue to use that temporary tablespace.

See Also:

Oracle Database Administrator's Guide for the complete steps for creating a PDB by cloning an existing PDB

FROM Clause

Use src_pdb_name to specify the name of the source PDB. The source PDB must be open with open mode READ ONLY. The files associated with the source PDB are copied to a new location and these copied files are then associated with the new PDB.

You cannot specify PDB$SEED for src_pdb_name. Instead, use the create_pdb_from_seed clause to create a PDB by using the seed as a template.

dblink

If the source PDB is in a remote CDB, then use dblink to specify the name of the database link to use to connect to the remote CDB.

pdb_storage_clause

Use this clause to specify storage limits for the new PDB. Refer to pdb_storage_clause for the full semantics of this clause.

file_name_convert

Use this clause to determine how the database generates the names of files for the new PDB. Refer to file_name_convert for the full semantics of this clause.

path_prefix_clause

Use this clause to ensure that all directory object paths and paths contained in certain parameters associated with the PDB are restricted to the specified directory or its subdirectories. Refer to path_prefix_clause for the full semantics of this clause.

tempfile_reuse_clause

Specify TEMPFILE REUSE to instruct the database to format and reuse a temp file associated with the new PDB if it already exists. Refer to tempfile_reuse_clause for the full semantics of this clause.

SNAPSHOT COPY

Specify SNAPSHOT COPY if the source PDB files are stored on either Oracle Automatic Storage Management Cluster File System (Oracle ACFS) or Direct NFS Client storage.

The following requirements must be met to use the SNAPSHOT COPY clause:

  • All of the data files of the source PDB must be stored in the same storage type.

  • The source PDB must be in the local CDB; it cannot be in a remote CDB.

Direct NFS Client enables an Oracle database to access network attached storage (NAS) devices directly, rather than using the operating system kernel NFS client. If the PDB files are stored on Direct NFS Client storage, then the following additional requirements must be met:

  • The source PDB files must be located on an NFS volume.

  • Storage credentials must be stored in a Transparent Data Encryption keystore.

  • The storage user must have the privileges required to create and destroy snapshots on the volume that hosts the source PDB files.

  • Credentials must be stored in the keystore using an ADMINISTER KEY MANAGEMENT ADD SECRET SQL statement.

When you use the SNAPSHOT COPY clause to create a clone of a source PDB, the following restrictions apply to the source PDB as long as any clones exist:

  • It cannot be unplugged.

  • It cannot be dropped.

Storage clones are named and tagged using the new PDB GUID. You can query the CLONETAG column of DBA_PDB_HISTORY view to view clone tags for storage clones.

create_pdb_from_xml

This clause enables you to create a PDB by plugging an unplugged PDB or a non-CDB (the source database) into a CDB (the target CDB). If the source database is an unplugged PDB, then it may have been unplugged from the target CDB or a different CDB.

The source database and the target CDB must meet the following requirements:

  • They must have the same endian format.

  • They must have compatible character sets and national character sets, which means:

    • Every character in the source database character set is available in the target CDB character set.

    • Every character in the source database character set has the same code point value in the target CDB character set.

  • They must have the same set of database options installed.

See Also:

AS CLONE Clause

Specify this clause only if the target CDB already contains a PDB that was created using the same set of data files. The source files remain as an unplugged PDB and can be used again. Specifying AS CLONE also ensures that Oracle Database generates new identifiers, such as DBID and GUID, for the new PDB. If you specify AS CLONE, then you cannot specify the MOVE or NOCOPY clauses.

USING Clause

Use filename to specify the name of the XML file that contains the metadata that describes the source database that you are plugging in. The XML file and the files associated with the source database must exist in a location that is accessible to the CDB. You can obtain this file as follows:

  • If the source database is an unplugged PDB, then the XML metadata file was created by the pdb_unplug_clause of ALTER PLUGGABLE DATABASE.

  • If the source database is a non-CDB, then you must create the XML metadata file using the DBMS_PDB package.

source_file_name_convert

Specify this clause only if the contents of the XML file do not accurately describe the the locations of the source files. If the files that must be used to plug in the source database are no longer in the location specified in the XML file, then use this clause to map the specified file names to the actual file names.

  • For filename_pattern, specify the string for the location of the files as specified in the XML file.

  • For replacement_filename_pattern, specify the string for the actual location that contains the files that must be used to create the PDB.

Oracle Database will replace filename_pattern with replacement_filename_pattern when searching for the source database files.

File name patterns cannot match files or directories managed by Oracle Managed Files.

If the files that must be used to create the PDB exist in the location specified in the XML file, you can either omit this clause or specify SOURCE_FILE_NAME_CONVERT=NONE.

COPY Clause

Specify COPY if you want the files listed in the XML file to be copied to the new location and used for the new PDB. This is the default. You can use the optional file_name_convert clause to use pattern replacement in the new file names. Refer to file_name_convert for the full semantics of this clause. You cannot specify COPY if you specify the AS CLONE clause.

MOVE Clause

Specify MOVE if you want the files listed in the XML file to be moved, rather than copied, to the new location and used for the new PDB. You can use the optional file_name_convert clause to use pattern replacement in the new file names. Refer to file_name_convert for the full semantics of this clause. You cannot specify MOVE if you specify the AS CLONE clause.

NOCOPY Clause

Specify NOCOPY if you want the files for the PDB to remain in their current locations. Use this clause if there is no need to copy or move the files required to plug in the PDB.

pdb_storage_clause

Use this clause to specify storage limits for the new PDB. Refer to pdb_storage_clause for the full semantics of this clause.

path_prefix_clause

Use this clause to ensure that all directory object paths and paths contained in certain parameters associated with the PDB are restricted to the specified directory or its subdirectories. Refer to path_prefix_clause for the full semantics of this clause.

tempfile_reuse_clause

Specify TEMPFILE REUSE to instruct the database to format and reuse a temp file associated with the new PDB if it already exists. Refer to tempfile_reuse_clause for the full semantics of this clause.

Examples

Creating a PDB by Using the Seed: Example The following statement creates a PDB salespdb by using the seed in the CDB as a template. The administrative user salesadm is created and granted the dba role. The default tablespace assigned to any non-SYSTEM user for whom no permanent tablespace is assigned is sales. File names for the new PDB will be constructed by replacing /disk1/oracle/dbs/pdbseed/ in the file names in the seed with /disk1/oracle/dbs/salespdb/. All tablespaces that belong to sales must not exceed 2G. The location of all directory object paths and paths contained in certain parameters associated with salespdb are restricted to the directory /disk1/oracle/dbs/salespdb/.

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

Cloning a PDB From an Existing PDB: Example The following statement creates a PDB newpdb by cloning PDB salespdb. PDBs salespdb and newpdb are in the same CDB. Because no storage limits are explicitly specified, there is no limit on the amount of storage for newpdb. The files are copied from /disk1/oracle/salespdb/ to /disk2/oracle/newpdb/. The location of all directory object paths and paths contained in certain parameters associated with newpdb are restricted to the directory /disk2/oracle/newpdb/.

CREATE PLUGGABLE DATABASE newpdb FROM salespdb
  FILE_NAME_CONVERT = ('/disk1/oracle/dbs/salespdb/', '/disk1/oracle/dbs/newpdb/')
  PATH_PREFIX = '/disk1/oracle/dbs/newpdb';

Plugging a PDB into a CDB: Example The following statement plugs the PDB mypdb, which was previously unplugged, into the CDB. The details about the metadata describing mypdb are stored in the XML file /disk1/user/mypdb.xml. The XML file does not accurately describe the current locations of the files. Therefore, the SOURCE_FILE_NAME_CONVERT clause is used to indicate that the files are in /disk2/oracle/dbs/mypdb/, not /disk1/oracle/dbs/mypdb/. The NOCOPY clause indicates that the files are already in the correct location. All tablespaces that belong to sales must not exceed 2G. A file with the same name as the temp file specified in the XML file exists in the target location. Therefore, the TEMPFILE REUSE clause is required.

CREATE PLUGGABLE DATABASE salespdb
  USING '/disk1/usr/salespdb.xml'
  SOURCE_FILE_NAME_CONVERT =
    ('/disk1/oracle/dbs/mypdb/', '/disk2/oracle/dbs/mypdb/')
  NOCOPY
  STORAGE (MAXSIZE 2G)
  TEMPFILE REUSE;