Leverage Oracle Pluggable Databases architecture

[loiclefevre]

Oracle database has introduced its Container architecture (aka Multitenant architecture) in 2013 with the 12cR1 version (21c XE release extended the maximum number of Pluggable Databases (PDBs) to 252).

A container database (aka CDB) comes with numerous capabilities that would be very useful for CI pipelines if they were available through some API:

• a PDB is a completely isolated database from an application perspective
• creating a PDB takes far less time to create (in the range of 10 seconds with 21c XE)
• a PDB can be created as a clone of a Baseline PDB where for instance the application schema (tables...) has been migrated to the proper version to test

DDL to create a new Pluggable database (based on Gerald's slim image 21c XE):

CREATE PLUGGABLE DATABASE testpdb2 ADMIN USER pdb_admin IDENTIFIED BY "test" ROLES=(DBA) 
PARALLEL NOLOGGING 
FILE_NAME_CONVERT=('/opt/oracle/oradata/XE/pdbseed/','/opt/oracle/oradata/XE/testpdb2/') 
TEMPFILE REUSE 
DEFAULT TABLESPACE USERS DATAFILE '/opt/oracle/oradata/XE/testpdb2/users01.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M

DDL to clone XEPDB1:

CREATE PLUGGABLE DATABASE testpdb4 FROM xepdb1 PARALLEL NOLOGGING CREATE_FILE_DEST='/opt/oracle/oradata/XE'

Of course, the new database user to be used for testing the application then needs to be created, for example like:

CREATE USER test IDENTIFIED BY "testPassword" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
ALTER USER temp QUOTA unlimited on users;
grant create session, select_catalog_role to test;
grant select any dictionary to test;
...

Thanks