Learn how to create Pluggable Database or PDB in Oracle

In our previous article, we learned how we can run an Oracle Database Image in Docker. The default image contains 1 PDB for you to use. In some cases, a single PDB is enough for development. But if you are working on multiple projects, you might want to create a separate PDB instance for each of those projects. In this tutorial, we will create a new PDB or pluggable database in Oracle.

Oracle Multitenant Design

Oracle supports multi-tenant design. A CDB or Container Database contains a collection of schemas, schema objects, and non schema objects to which all PDBs belong. A PDB or Pluggable Database, on the other hand, is a portable collection of schemas, schema objects, and non-schema objects that appears to an Oracle Net client as non-CDB. In short, you can create multiple database instances (PDB) in a single instance of Oracle. For more information about Oracle’s Multitenant Design, visit the official page in Oracle.

Create PDB in Oracle

Open your SQL Developer and connect as SYSDBA to your root container. If you have followed our article before, the SID should be ORCLCDB.

Verify that you can access the list of PDBs in your Oracle Instance. The command below should return the root CDB and 0 or more instances of PDB.

SELECT * FROM DBA_PDBS;

Next, we prepare our Oracle instance. On your machine where the Oracle was installed, create a new directory to hold the recovery files. In our case, we create this directory:

mkdir /opt/oracle/product/21c/dbhome_1/oradata
mkdir /opt/oracle/product/21c/dbhome_1/flash_recovery_area

If you are using docker, you can create the directory inside the docker container. You just need to enter the container using this and run again the mkdir command. Change the {containerName} to your own oracle docker name.

docker exec -it {containerName} bash

Then on your SQL Developer, type the following to create your recovery file destinations:

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/opt/oracle/product/21c/dbhome_1/oradata';
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/opt/oracle/product/21c/dbhome_1/flash_recovery_area';
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G;
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_1 = '/opt/oracle/product/21c/dbhome_1/oradata';

Now we’re ready to create the PDB. In the same SQL Developer, type the following. Change the PDB name, admin name, and password to whatever you like:

-- create the pdb
CREATE PLUGGABLE DATABASE javapointerspdb ADMIN USER admin IDENTIFIED BY admin ROLES=(DBA);
ALTER PLUGGABLE DATABASE javapointerspdb OPEN READ WRITE FORCE;

-- grant privileges
ALTER SESSION SET CONTAINER = javapointerspdb;
GRANT CREATE SESSION TO admin CONTAINER=CURRENT;
GRANT SYSDBA TO admin CONTAINER=CURRENT;

Testing your newly created PDB

To test your PDB, create a new database connection in your SQL Developer with the details you have added when creating the PDB. The user you have added when creating the PDB is the SYSDBA.

The service name should be used instead of the SID. The service name is the name of the pluggable database:

Deleting a PDB in Oracle

To delete a PDB, run the following command:

-- close first the pdb to kill any active sessions
ALTER PLUGGABLE DATABASE javapointerspdb CLOSE IMMEDIATE; 
DROP PLUGGABLE DATABASE javapointerspdb INCLUDING DATAFILES;