Oracle Multitenant

 Oracle Multitenant

Oracle Multitenant is a feature in Oracle Database that allows you to manage multiple pluggable databases (PDBs) as a single container database (CDB). This enables you to consolidate multiple databases onto a single server while still maintaining isolation between the individual databases.

Here is an example of using Oracle Multitenant to create a CDB and PDB:

-- Create the CDB

CREATE DATABASE cdb

  MAXINSTANCES 8

  MAXLOGHISTORY 1

  MAXLOGFILES 16

  MAXLOGMEMBERS 3

  MAXDATAFILES 100

  CHARACTER SET AL32UTF8;

-- Create the PDB

CREATE PLUGGABLE DATABASE pdb1

  ADMIN USER pdb_admin IDENTIFIED BY password

  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb/', '/u01/app/oracle/oradata/pdb1/')

  DEFAULT TABLESPACE users

  DEFAULT TEMPORARY TABLESPACE temp

  PROFILE pdb_profile;

-- Open the PDB

ALTER PLUGGABLE DATABASE pdb1 OPEN;

This example creates a CDB named "cdb" and a PDB named "pdb1". The PDB is created with an administrative user named "pdb_admin" with a password of "password", and is configured to use a file name conversion to store its files in a separate directory from the CDB. The PDB is also created with a default tablespace of "users" and a default temporary tablespace of "temp", and is assigned a profile named "pdb_profile". Finally, the PDB is opened using the ALTER PLUGGABLE DATABASE statement.

Once the CDB and PDB are created, you can manage them using a variety of SQL statements and PL/SQL packages. For example, you can switch between PDBs using the ALTER SESSION SET CONTAINER statement, create common users and roles that are visible across all PDBs using the CREATE COMMON USER and CREATE COMMON ROLE statements, and create local users and roles that are specific to individual PDBs using the CREATE USER and CREATE ROLE statements. You can also perform administrative tasks such as backup and recovery, performance tuning, and security management on the CDB and individual PDBs using a variety of SQL statements and PL/SQL packages.