Oracle 12c Container views - CDB_ Views

Today, I did get an opportunity to research on Containers and pluggable databases - the "single instance, multiple databases" concept that's one of the new features of Oracle 12c.

I was really curious to know where the container view data comes from, in other words, where does the data for the CDB_ views come from?

Well, this is what I found:

It is actually coming from a function:

SQL> select name,user,cdb from v$database;

NAME      USER                           CDB
--------- ------------------------------ ---
MY12CDB   SYS                            YES

1 row selected.

SQL> select dbms_metadata.get_ddl ('VIEW','CDB_OBJECTS') from dual;

DBMS_METADATA.GET_DDL('VIEW','CDB_OBJECTS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."CDB_OB
JECTS"  CONTAINER_DATA
 ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID",
 "DATA_OBJECT_ID", "OBJECT_TYPE", "CREAT
ED", "LAST_DDL_TIME", "TIMESTAMP", "STAT
US", "TEMPORARY", "GENERATED", "SECONDAR
Y", "NAMESPACE", "EDITION_NAME", "SHARIN
G", "EDITIONABLE", "ORACLE_MAINTAINED",
"CON_ID") AS
  SELECT "OWNER","OBJECT_NAME","SUBOBJECT_NAME
","OBJECT_ID","DATA_OBJECT_ID","OBJECT_T
YPE","CREATED","LAST_DDL_TIME","TIMESTAM
P","STATUS","TEMPORARY","GENERATED","SEC
ONDARY","NAMESPACE","EDITION_NAME","SHAR
ING","EDITIONABLE","ORACLE_MAINTAINED","
CON_ID" FROM CDB$VIEW("SYS"."DBA_OBJECTS
")


1 row selected.

Now, what is CDB$VIEW?

Upon further enquiry, I stumbled upon this wonderful blogpost that details the CDB Views. It provided a detailed explanation about the CDB Views in Oracle 12c.

Here are a queries for some of the common scenarios:

1. How many users have I created in each of the PDBs? How do I see it in one place?

Answer: The view used in CDB_USERS and here's the query:

select vp.con_id, vp.name, cu.user_id,cu.username
from
v$pdbs vp, cdb_users cu
where
cu.con_id = vp.con_id
and account_status = 'OPEN'
order by 1,3;

2. That's well and good. Now, how do I know how many objects have been created by each of these users?

Answer: Here you go:

col owner for a30
col con_id for 99999999999999999
COMPUTE SUM LABEL 'NUM_OBJ_IN_PDB' OF NUM_OBJ ON CON_ID
BREAK ON CON_ID SKIP 1
select con_id, owner, count(1) "NUM_OBJ" from cdb_objects
group by con_id,owner
order by 1,3;

3. Which are the datafiles and tablespaces associated with the PDBs and how to list them?

select vp.con_id, vp.name, cdf.file_id, cdf.file_name, cdf.tablespace_name, cdf.bytes/1024/1024 SIZE_MB, status 
from v$pdbs vp, cdb_data_files cdf
where
vp.con_id = cdf.con_id
order by 1,3;

A few helpful links in understanding Container and pluggable databases:

1. Oracle 12c Multitenant Feature

2. CDB and PDB Views