How to Creating a PDB, Oracle 12c on Windows

on

How to Creating a PDB, Oracle 12c on Windows 




  • Creating a PDB, Oracle provides many statements for creating PDBs from the PDBSEED database. 

#Step  - 1. Use FILE_NAME_CONVERT in syntax. You need to check the file location of the PDB$SEED database.
C:\Oracle>sqlplus '/ as sysdba'

 

SQL> select FILE_NAME from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
C:\ORACLE\APP\PRODUCT\18.0.0\ORADATA\XE\USERS01.DBF
C:\ORACLE\APP\PRODUCT\18.0.0\ORADATA\XE\UNDOTBS01.DBF
C:\ORACLE\APP\PRODUCT\18.0.0\ORADATA\XE\SYSTEM01.DBF
C:\ORACLE\APP\PRODUCT\18.0.0\ORADATA\XE\SYSAUX01.DBF

SQL> CREATE PLUGGABLE DATABASE XEPDB2 ADMIN USER pdb_adm IDENTIFIED BY pdb_adm 
FILE_NAME_CONVERT=('C:\ORACLE\APP\PRODUCT\18.0.0\ORADATA\XE\PDBSEED\',
'C:\ORACLE\APP\PRODUCT\18.0.0\ORADATA\XE\XEPDB2\');

#Step  - 2. Use Oracle Managed files method.

SQL>CONN / AS SYSDBA
SQL>ALTER SYSTEM SET db_create_file_dest = 'C:\ORACLE\APP\PRODUCT\18.0.0\ORADATA 
\XE'; 
SQL>CREATE PLUGGABLE DATABASE XEPDB2 ADMIN USER pdb_adm IDENTIFIED BY pdb_adm;

 #Step - 3. You have an alternative method for 1 step.

SQL> CONN / AS SYSDBA
SQL>ALTER SESSION SET PDB_FILE_NAME_CONVERT= 
'C:\ORACLE\APP\PRODUCT\18.0.0\ORADATA\XE\PDBSEED\', 
'C:\ORACLE\APP\PRODUCT\18.0.0\ORADATA\XE\XEPDB2\';
SQL>CREATE PLUGGABLE DATABASE XEPDB2 ADMIN USER pdb_adm IDENTIFIED BY pdb_adm;

  • Open the new (PDB) by running the below script 

SQL> alter pluggable database XEPDB2 close immediate;
SQL> alter pluggable database XEPDB2 open read only;
SQL> alter pluggable database XEPDB2 open read write;

  • Switching to new (PDB) Containers

SQL> alter session set container=XEPDB2;

  • Displaying the Current Container

SQL>COLUMN pdb_name FORMAT A20
SQL>SELECT pdb_name, status FROM dba_pdbsORDER BY pdb_name;
SQL>COLUMN name FORMAT A20
SQL>SELECT name, open_mode FROM v$pdbs ORDER BY name;
SQL> SHOW PDBS;

  • Delete a Pluggable Database (PDB) Manually

SQL> ALTER PLUGGABLE DATABASE XEPDB2 CLOSE;
SQL> DROP PLUGGABLE DATABASE XEPDB2 KEEP DATAFILES;
SQL> DROP PLUGGABLE DATABASE XEPDB2 INCLUDING DATAFILES;

The End.