CREATE PLUGGABLE DATABASE Syntax

主要分为三个子句：

Create_pdb_from_seed

Create_pdb_clone

Create_pdb_from_xml

1. AS APPLICATION CONTAINER Clause

用于创建application container, 而不是application container下的pdb(即application pdb)

执行时当前container必须为CDB$root而不能为application root.

创建application pdb方法与一般pdb方法相同，当前container为root即创建pdbs，当前container为application container，则创建application pdbs.

The SQL statements that create PDBs and application PDBs are exactly the same. When a CREATE PLUGGABLE DATABASE statement is run in the CDB root, the PDB belongs to the CDB root. When a CREATE PLUGGABLE DATABASE statement is run in an application root, the application PDB belongs to the application root.

You can create an application root by running a CREATE PLUGGABLE DATABASE statement with the AS APPLICATION CONTAINER clause when the CDB root is the current container.

1. AS SEED Clause

用于创建application seed.当前container必须为application root.

An application container can have no application seed or one application seed. It cannot have more than one application seed. After an application seed is created, you can use it to create application PDBs in the application container. The application seed provides a quick way to create application PDBs that meet the requirements of the application container.

（一）create_pdb_from_seed::=

注：这部分列出了常用选项，部分选项是可以同时在Create_pdb_from_seed，Create_pdb_clone，Create_pdb_from_xml下使用的

ADMIN USER: Oracle Database will create a local user in the PDB and grants the PDB_DBA local role to that user.

1. pdb_dba_roles::=

表示给指定role授予给pdb_dba role，即增加pdb_dba角色权限

This clause lets you grant one or more roles to the PDB_DBA role.

2. parallel_pdb_creation_clause::=

指定是否使用并行执行进程来复制PDB数据文件到新Location，只用于使用PDB$SEED或application seed来创建pdb时

You can use this clause to make the creation of PDBs faster by using more than one parallel execution server to copy the new PDB’s data files to a new location. If you specify a value of 0 or 1, then the CDB does not parallelize the creation of the PDB. This can result in a longer PDB creation time.

When the compatibility level of the CDB is 12.2.0 or higher, the CDB chooses the degree of parallelism to use for PDB creation by default. Alternatively, you can specify the desired degree of parallelism in the clause, but the CDB can ignore the suggestion, depending on the current database load and the number of available parallel execution servers.

1. default_tablespace::=

用于指定新PDB的non-system用户的默认表空间

如果使用PDB$SEED或是application seed来创建PDB时指定default tablespace, oracle会为新PDB使用smallfile创建此表空间；如果使用其它方式创建PDB，则default tablespace必须是source PDB or non-CDB已存在的表空间, 此时也无法指定datafile子句或是extent_managemnet_clause

DEFAULT TABLESPACE sales

4. pdb_storage_clause::=

用于限制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.

Use MAX_AUDIT_SIZE to limit the amount of storage that can be used by unified audit OS spillover (.bin format) files in the PDB to the value specified with size_clause.

Use MAX_DIAG_SIZE to limit the amount of storage for diagnostics (trace files and incident dumps) in the Automatic Diagnostic Repository (ADR) that can be used by the PDB to the value specified with size_clause.

STORAGE (MAXSIZE 2G MAX_AUDIT_SIZE=2G MAX_DIAG_SIZE=2G)

5. service_name_convert::=

用于转化源PDB中用户定义的services name, 以避免在新CDB中冲突

For service_name, specify the name of a service found in the PDB seed (when creating a PDB in an application container by using the application seed) or in the source PDB (when cloning a PDB or plugging a PDB into a CDB).

You cannot specify this clause when you use the create_pdb_from_seed clause to create a PDB from the CDB seed, because the CDB seed does not have user-defined services. You can, however, specify this clause when you use the create_pdb_from_seed clause to create an application PDB from the application seed.

Note: This clause does not apply to the default service for the PDB. The default service has the same name as the PDB.如果冲突只能更改PDB名字

SERVICE_NAME_CONVERT = ('salesrep','salesperson')

1. path_prefix_clause::=

使用此子句可确保与PDB关联的目录对象的文件路径加上此前缀，它同时还会影响如下目录: the Oracle XML repository for the PDB, files created with a CREATE PFILE statement, and the export directory for Oracle wallets.

Note:The PATH_PREFIX clause does not apply to data files or temporary files.

Use this clause to ensure that file paths for directory objects associated with the PDB are restricted to the specified directory or its subdirectories. This clause also ensures that the following files associated with the PDB are restricted to the specified directory: the Oracle XML repository for the PDB, files created with a CREATE PFILE statement, and the export directory for Oracle wallets.

After the path_prefix_clause is specified for a PDB, existing directory objects might not work as expected, since the PATH_PREFIX string is always added as a prefix to all local directory objects in the PDB. The path_prefix_clause only applies to user-created directory objects. It does not apply to Oracle-supplied directory objects.

You cannot modify the setting of this clause after you create the PDB.

语法中的directory_object_name是root中定义的directory，The directory object points to the absolute path to be used for PATH_PREFIX.

注linux一定要以/结尾，它会append到file names上

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/dbs/salespdb/'

1. tempfile_reuse_clause::=

创建pdb时会复制源库的所有临时表空间(包括非默认)，这里只是创建文件时有相同名则reuse

If you do not specify this clause, 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.

TEMPFILE REUSE

1. user_tablespaces_clause::=

用于指定哪些表空间在新PDB中可用，如果每个schema使用不同表空间，它可以实现同库中不同schema分离到不同PDB中.

注：如果指定值为NONE则新PDB只有SYSTEM, SYSAUX, TEMP表空间可用，默认为ALL

When the compatibility level of the CDB is 12.2.0 or higher, the tablespaces that are excluded by this clause are created offline in the new PDB, and they have no data files associated with them. When the compatibility level of the CDB is lower than 12.2.0, 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.

如果user tablespace(除system,sysaux, temp tablespace )的创建方式与全局设置不同，可以在此子句后指定创建方式，它会覆盖全局设置

This example shows a full CREATE PLUGGABLE DATABASE statement that plugs in a non-CDB and only includes the tbs3 user tablespace from the non-CDB. The example copies the files for Oracle-supplied tablespaces (such as SYSTEM and SYSAUX) to a new location, but moves the files of the tbs3 user tablespace.

CREATE PLUGGABLE DATABASE ncdb USING '/disk1/oracle/ncdb.xml'

  COPY

  FILE_NAME_CONVERT = ('/disk1/oracle/dbs/', '/disk2/oracle/ncdb/')

  USER_TABLESPACES=('tbs3') MOVE;

关于creation mode：

1. { SNAPSHOT COPY | NO DATA }

只用于使用create_pdb_clone子句的cloning方式创建PDB，但不能用于cloning a non-CDB

By default, the database creates each tablespace to be made available in the new PDB according to the settings specified for cloning the PDB. These clauses allow you to override those settings

1. SNAPSHOT COPY 

Clone the tablespace using storage snapshots. This reduces the time required to create the clone because the database does not need to make a complete copy of the source data files.

When you use the SNAPSHOT COPY clause, all of the data files of the source PDB must be stored in the same storage type.

When you use the SNAPSHOT COPY clause to create a clone of a source PDB and the CLONEDB initialization parameter is set to FALSE, the underlying file system for the source PDB's files must support storage snapshots. Such file systems include Oracle Automatic Storage Management Cluster File System (Oracle ACFS) and Direct NFS Client storage.

When you use the SNAPSHOT COPY clause to create a clone of a source PDB and the CLONEDB initialization parameter is set to TRUE, the underlying file system for the source PDB's files can be any local file system, network file system (NFS), or clustered file system that has Direct NFS enabled. However, the source PDB must remain in open read-only mode as long as any clones exist.

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:

1. The source PDB files must be located on an NFS volume.
2. Storage credentials must be stored in a Transparent Data Encryption keystore.
3. The storage user must have the privileges required to create and destroy snapshots on the volume that hosts the source PDB files.
4. 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.

使用snapshot copy创建的PDB不能被unplugged

PDB clones created using the SNAPSHOT COPY clause cannot be unplugged. They can only be dropped. Attempting to unplug a clone created using the SNAPSHOT COPY clause results in an error.

For a PDB created using the SNAPSHOT COPY clause in an Oracle Real Application Clusters (Oracle RAC) environment, each node that must access the PDB's files must be mounted. For Oracle RAC databases running on Linux or UNIX platforms, the underlying NFS volumes must be mounted. If the Oracle RAC database is running on a Windows platform and using Direct NFS for shared storage, then you must update the oranfstab file on all nodes with the created volume export and mount entries.

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.

1. NO DATA: 只复制元信息不复制data

This clause specifies that the source PDB's data model definition is cloned, but not the PDB's data. This clause is useful for quickly creating clones of a PDB with only the object definitions and no data. 

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.

You cannot specify NO DATA if the source PDB contains clustered tables, Advanced Queuing (AQ) tables, index-organized tables, or tables that contain abstract data type columns.

1. { COPY | MOVE | NOCOPY }

These clauses apply when you plug in a PDB with the create_pdb_from_xml clause. By default, the database creates each tablespace to be made available in the new PDB according to the settings specified for plugging in the PDB. These clauses allow you to override those settings

1. COPY

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.

1. MOVE - Move the tablespace files to the new location.

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.

1. NOCOPY - Do not copy or move the tablespace files to the new location.

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.

1. standbys_clause::=

不太清楚standby cdb的概念没看

You can specify one of the following values for the STANDBYS clause:

1. ALL includes the new PDB in all of the standby CDBs.
2. ALL EXCEPT includes the new PDB in all of the standby CDBs except the specified standby CDBs. If more than one standby CDB is excluded, use a comma-separated list of the excluded standby CDBs.
3. A comma-separated list of the standby CDBs. The new PDB is included in the listed standby CDBs.
4. 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.

Use this clause to specify whether the new PDB is included in one or more standby CDBs. If you include a PDB in a standby CDB, then during standby recovery the standby CDB will search for the data files for the PDB. If the data files are not found, then standby recovery will stop and you must copy the data files to the correct location before you can restart recovery.

Specify cdb_name to include the new PDB in the specified standby CDB. You can specify more than one standby CDB name in a comma-separated list.

Specify ALL to include the new PDB in all standby CDBs. This is the default.

Specify ALL EXCEPT to include the new PDB in all standby CDBs, except the specified standby CDBs.

Specify NONE to exclude the new PDB from all standby CDBs. When a PDB is excluded from all standby CDBs, the PDB's data files are unnamed and marked offline on all of the standby CDBs. Standby recovery will not stop if the data files for the PDB are not found on the standby. If you instantiate a new standby CDB after the PDB is created, then you must explicitly disable the PDB for recovery on the new standby CDB.

You can enable a PDB on a standby CDB after it was excluded on that standby CDB by copying the data files to the correct location, bringing the PDB online, and marking it as enabled for recovery.

1. logging_clause::=

用于指定新PDB表空间默认为LOGGING还是NOLOGGING，通过DBA_PDBS视图可查看PDB此属性

The logging attribute controls whether certain DML operations are logged in the redo log file (LOGGING) or not (NOLOGGING).The default is LOGGING. 

11. file_name_convert::=

如果用于seed或cloning a pdb创建pdb，表示把它们的数据文件位置进行转化；用于XML文件时，把xml文件中记录的数据文件位置进行转化

下面/disk1/替代为/disk2/, /oracle/dbs/替代为/oracle/pdb5/

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

12. create_file_dest_clause::=

用于指定新创建PDB文件使用的OMF目录：

1. 如果未指定此选项且root使用DB_CREATE_FILE_DEST初始参数，则新PDB将继承此设置；

The PDB shares the same base file system directory for Oracle Managed Files with the root and has its own subdirectory named with the GUID of the PDB.

1. 如果指定此选项为NONE，则新PDB将禁用OMF
2. 如果指定此选项为非none值，则将隐式把PDB的DB_CREATE_FILE_DEST参数设置为此值

1. If CREATE_FILE_DEST=NONE is specified, then Oracle Managed Files is disabled for the PDB.
2. 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 with SCOPE=SPFILE.
3. If the CDB root uses Oracle Managed Files, and this clause is not specified, then the PDB inherits the Oracle Managed Files default location from the CDB root.

Create_file_dest_clause/file_name_convert_clause与root初始参数优先级：

Note: Create_file_dest_clause/file_name_convert_clause只影响是否创建PDB时使用OMF, 之后创建的文件是否使用OMF还要看DB_CREATE_FILE_DEST的选项设置

CREATE_FILE_DEST = '/oracle/pdb2/'

1. HOST and PORT

These clauses are useful only if you are creating a PDB that you plan to reference from a proxy PDB. This type of PDB is called a referenced PDB.

A proxy PDB uses a database link to establish communication with its referenced PDB. After communication is established, the proxy PDB communicates directly with the referenced PDB without using a database link. The host name and port number of the listener for the referenced PDB must be correct for the proxy PDB to function properly.

host默认为referenced PDB 所在主机名，port默认为1521

（二）create_pdb_clone::=

This clause enables you to create a new PDB by cloning a source to a target PDB. The source can be a PDB in the local CDB, a PDB in a remote CDB, or a non-CDB. The target PDB is the clone of the source.

1. AS PROXY FROM

Use this clause to create a proxy PDB by referencing a different PDB, which is referred to as the referenced PDB. The referenced PDB can be in the same CDB as the proxy PDB or in a different CDB. A local proxy PDB is in the same CDB as its referenced PDB, and a remote proxy PDB is in a different CDB than its referenced PDB.

1. RELOCATE

The database first clones the source PDB to the target PDB, and then removes the source PDB.

1. keystore_clause::=

Specify this clause if the source database has encrypted data or a keystore set. For keystore_password, specify the password for the keystore. You must provide this password even if the source database is using an auto-login software keystore. You can find if the source database has encrypted data by querying the DBA_ENCRYPTED_COLUMNS data dictionary view or the V$ENCRYPTED_TABLESPACES dynamic performance view.

2. pdb_refresh_mode_clause::=

只用于CREATE PLUGGABLE DATABASE ... FROM创建pdb

新PDB会从旧PDB refresh changes

可以使用ALTER PLUGGABLE DATABASE切换refresh mode, 可以在manual/auto间相互切换，但如果切换为none就不能再切回来了(A PDB that is not refreshable cannot be changed into a refreshable PDB)

Auto refresh mode下同样可以使用manual(alter pluggable database pdbname refresh)来刷新

Note：

1. A refreshable PDB and its source must be in different CDBs. Therefore, a refreshable PDB must be created at a CDB that is remote from the source CDB, and a database link must be specified during the creation.
2. A refreshable PDB can be opened only in open read-only mode. Also, a refreshable PDB must be closed when a refresh is performed. If it is not closed when automatic refresh is attempted, then the refresh is deferred until the next scheduled refresh. If it is not closed when a user attempts to perform manual refresh, then an error is reported.
3. When you create a refreshable PDB, you can set the REMOTE_RECOVERY_FILE_DEST initialization parameter in the PDB. This initialization parameter specifies a directory from which to read archive log files during refresh operations if the source PDB is not available over its database link.
4. If new data files are created in the source PDB, then the PDB_FILE_NAME_CONVERT initialization parameter must be set in the CDB to convert the data file paths from the source PDB to the clone PDB.

（三）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).

AS CLONE

在相同CDB中使用相同unplugged PDB创建多个PDB时，指定此子句生成不同数据库ID.

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.

1. source_file_name_convert::=

用于plugging in an unplugged PDB时使用xml文件指定时，xml文件会包含PDB数据文件位置，如果这个位置不准确则需要使用source_file_name_convert子句转化

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

When you specify an XML metadata file when plugging in a PDB, this file describes the names and locations of an unplugged PDB's source files. The XML file might not describe the locations 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, 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/')

1. source_file_directory::=

同样用于plugging in an unplugged PDB时使用xml文件指定，而XML中数据文件位置不准确，   如果unplugged pdb数据文件较多，使用source_file_name_convert可能很麻烦，可以把全部文件放到同一目录，然后使用source_file_directory子句指定

The clause specifies a directory that contains all of the files listed in the XML file. Using this clause is convenient when you have a large number of data files and specifying a SOURCE_FILE_NAME_CONVERT pattern for each file is not feasible.

Note:source_file_directory and source_file_name_convert are mutually exclusive.

SOURCE_FILE_DIRECTORY = '/oracle/pdb5/'

You can specify this clause for configurations that use Oracle Managed Files and for configurations that do not use Oracle Managed Files.

Note: :source_file_directory and source_file_name_convert不用于使用.pdb archive file

When you use a .pdb archive file when plugging in a PDB, this file is extracted when you plug in the PDB, and the PDB’s files are placed in the same directory as the .pdb archive file. Therefore, the clauses that specify the source file locations are not required when you use a .pdb archive file.