Database Reference
In-Depth Information
The stroke of genius applied for Pluggable Databases is quite simple in theory: instead of allowing user data to
“pollute” the Oracle namespace in the CDB$ROOT Database, there is a strict separation of what is stored in the PDB vs.
the CDB. Instead of duplicating each row of the data dictionary on each PDB, only the delta is stored within the PDB.
The remaining information exists as a pointer from the PDB to the data in the CDB. This not only conserves a lot of space
since the PDB's dictionary does not contain redundant information, but it also makes the PDB a lot more independent.
This is an important aspect when it comes to unplugging/re-plugging a PDB from one CDB to another.
You already guessed it: the introduction of containers made it necessary to add metadata about them. When
researching this topic I often found myself selecting from the DBA_% views—those used to contain everything before
Oracle 12.1. If you did not find what you were looking for in a DBA% view it probably did not exist. In the current
release, such views no longer return all that you might expect. The confusion is quite easily lifted if you consider the
following rules:
•
The DBA% views list all the information in the current container, but not sub-containers. In
other words, you can for example view all the common users in the CDB$ROOT, but it won't
show you the users in the seed PDB or any user-defined PDB.
•
The same rule applies for the USER% and ALL% views—their scope is the current container.
•
Caveat: the new class of CDB% views does not list information about mounted PDBs. To view
dictionary information about a PDB it has to be opened read only or read write.
•
The V$-type views list information about PDBs as well when you query them from the
CDB$ROOT
The following two queries return the same result, but have been executed in different containers. Both queries
achieve the same goal: they list all the users for container 3.
First, here is a query executed from the CDB:
SYS@CDB$ROOT> select username,authentication_type,common
2 from cdb_users
3 where con_id = 3
4 order by username;
USERNAME AUTHENTI COM
------------------------------ -------- ---
ANONYMOUS PASSWORD YES
APEX_040200 PASSWORD YES
APEX_PUBLIC_USER PASSWORD YES
APPQOSSYS PASSWORD YES
[...]
45 rows selected.
SQL>
Next is a query executed as SYSTEM while logged on to the PDB:
SYSTEM@PDB1> select username,authentication_type,common
2 from dba_users
3 order by username;
Search WWH ::
Custom Search