Connecting to a CDB or PDB
- Connecting to a CDB or PDB is not different of connecting to a standard database.
- When you create a PDB, it is automatically assigned a service which can be used to reach this specific PDB.
- You can either use the EZ connect syntax, the Oracle Net Service syntax or the new : ALTER SESSION SET CONTAINER syntax.
- The CDB_SERVICES view lets you view the services for each PDB.
- The SHOW CON_NAME command can be used to identify the container your are connected to.
Users and administrative tasks
- Only a common user can connect to CDB$ROOT.
- To use the ALTER SESSION SET CONTAINER command, a common user must have the SET CONTAINER privilege.
- All management tasks except PLUG/UNPLUG and STOP/START in a PDB are equivalent to a standard database.
- You can use the CONTAINER=ALL clause in a DDL statement to execute it in all PDBs. This can only be done by a common user with the SET CONTAINER privilege.
- By DEFAULT when you issue a DDL the CONTAINER clause is equal to CURRENT.
- A common user name starts wth C##
Starting and Stopping CDBs and PDBs
- Stopping and starting a CDB is not different from a standard database.
- As there is only one instance (the CDB) a PDB can’t be opened if the CDB is not.
- Starting/stoping a PDB :
- ALTER PLUGGABLE DATABASE MY_PDB OPEN/CLOSE;
- Starting/stoping all PDBs :
- ALTER PLUGGABLE DATABASE ALL OPEN/CLOSE;
- Starting/stoping all PDBs except one :
- ALTER PLUGGABLE DATABASE ALL EXCEPT ONE_PDB OPEN/CLOSE;
- By default all PDBs are mounted when you open a CDB
- A PDB can be :
- OPEN READ WRITE
- OPEN READ ONLY
- OPEN MIGRATE
- the V$PDBS view can be used to see the OPEN_MODE of each PDB.
Changing parameters for a CDB/PDB
- Only the CDB has a SPFILE
- Some parameters can be modified at the PDB level, you can find them by querying the V$PARAMETER view : select name, value from v$parameter where ispdb_modifiable=’TRUE’;
- All PDBs will inherit parameters from the CDB if you don’t set them at the PDB level.
- All instance parameters are only alterable at the CDB level.
Querying management views in a multitenant architecture
- CDB_XXX views show information about the CDB$ROOT and all the PDBS
- DBA_XXX views show information for the current container (PDB or CDB)
- ALL_XXX views show information about objects a user can access inside a specific container.
- USER_XXX views show information about objects owner by a user inside a specific container.
- V$CONTAINERS displays information about PDBs and the root associated with the current instance.
- V$PDBS displays information about PDBs associated with the current instance.
- CDB_PDB_HISTORY shows the history of the PDBs in a CDB. It provides information about when and how each PDB was created and other information about each PDB’s history.
Users, Privileges and Roles in a multitenant architecture
- A common user is a user which belongs to the CDB$ROOT but is known by all the PDBs too. They can perform action in the root as well as in some PDBs if they are granted privileges to do so.
- Common user name always start with C##
- To create a common user, a common user must have the CREATE USER and SET CONTAINER privileges.
- Local users are only known in a specific PDB, they can’t perform actions outside the scope of the PDB where they have been created. They can’t be created in the root.
- Local users can’t grant common privileges to a common user.
- Local users can grant local privileges to a common user.
- rivileges can be LOCAL or COMMON too
- A LOCAL user can only be granted LOCAL privileges
- A COMMON user can have both LOCAL and COMMON privileges.
- Roles can be LOCAL or COMMON, as for username, a common role name always start with C##
- Unlike privileges a LOCAL user can be granted both LOCAL roles and COMMON roles.