The CREATE PLUGGABLE DATABASE statement can be used to create a PDB by copying the seed database’s data files. To do this, first, connect to the root container database as the SYS user (or a common user with create PDB privileges):$ sqlplus sysuser/ Cr4zyPa$$word1@mmdb23c as sysdba.
The following SQL statement creates a pluggable database named SALESPDB:
SQL> create pluggable database salespdb
admin user salesadm identified by “Cr4zyPa$$word1” file_name_convert = (‘/u01/app/oracle/oradata/mmdb23/pdbseed’, ‘/u01/app/oracle/oradata/mmdb23/salespdb’);
After running the prior code, you should see some output similar to this:
Pluggable database created.
Here’s a simplified statement when using standard files and ASM:
SQL> create pluggable database salespdb admin user salesadm identified by “Cr4zyPa$$word1”;
Pluggable database created.
Cloning an Existing PDB
You can create a PDB from an existing PDB within the currently connected (local) CDB, or you can create a PDB as a copy of a PDB from a remote CDB. Creating a copy is useful for test environments or needing to troubleshoot problems in production. These two techniques are detailed in the next two sections.
Local
In this example, an existing PDB (SALESPDB) is used to create a new PDB (SALESPDB2). First, connect to the root container, and place the existing source PDB in read-only mode:
$ sqlplus sysuser/Cr4zyPa$$word1@mmdb23c as sysdba
SQL> create pluggable database salespdb2 from salespdb;
You can also do this from a snapshot:
SQL> alter pluggable database mmpdb snapshot mmpdb1_623;
SQL> create pluggable database mmpdb_copy from mmpdb using snapshot mmpdb1_623;
Remote
You can also create a PDB as a clone of a remote PDB. First, you need to create a database link from the CDB to the PDB that will serve as the source for the clone.
Both the local user and the user specified in the database link must have the CREATE PLUGGABLE DATABASE privilege.
This example shows a local connection as SYS to the root container. This is the database in which the new PDB will be created:
$ sqlplus sysuser/Cr4zyPa$$word1@mmdb23 as sysdba
In this database, create a database link to the PDB in the remote CDB. The remote CDB contains a PDB named mmpdb, with a user that has been created with the CREATE PLUGGABLE DATABASE privilege granted to it. This is the user that will be used in the database link:
SQL> create database link mmpdb_connect
connect to pdbcloneuser identified by “Cr4zyPa$$word1” using ‘mm23c:1521/mmpdb’;
Next, connect to the remote database that contains the PDB that will be cloned:
$ sqlplus sysuser/Cr4zyPa$$word1@mm23c:1521/mmpdb as sysdba
SQL> create pluggable database mmpdb_clone from mmpdb@mmpdb_connect;
