. So here is the query:<\/p>\n And the first rows of the result where sorting those that have lot of lines in PDB$SEED first:<\/p>\n You see immediately that the largest metadata, which is the source of the stored procedures, in SOURCE$, is mostly stored only in CDB$ROOT. For space efficiency this is good.<\/p>\n However you can see that COL$ and TAB$ have same number of rows in CDB$ROOT and in PDB, which is not exactly what is described in the oracle documentation.<\/p>\n And tables such as DEPENDENCY$, managing dependency among objects, is still huge in the PDB. Dependencies are managed at that level.<\/p>\n This explain why it still takes time to upgrade or patch a PDB when the CDB has been upgraded or patched. There is not only the metadata\/data links to verify. There is still some DDL to run to maintain the pluggable database dictionary.<\/p>\n
\nMultitenant architecture is about dictionary separation. The idea is that all system metadata is stored only in CDB$ROOT so that space and upgrade time are optimized. Is it entirely true? let’s count the rows in the dictionary tables.
\n
\nIn order to verify that, I’ve build a query that will count the rows from the dictionary tables, in CDB$ROOT and in PDB$SEED.
\nThe idea is to query DBA_OBJECTS for ORACLE_MAINTAINED=Y objects and call a function that run an ‘execute immediate’ to do a ‘select count(*)’. Inline functions in 12c<\/em> are great for that. Especially when I want to switch to another container for it. Note that I’m not 100% sure that it’s supported to switch to another container there but al least don’t forget to switch back to initial one.
\nAs I’m using some inline function, I’ve added one ‘hextoasc’ that helps me to lookup into the dictionary cache for the presence of object (not related to this post) and I also check which table is in the bootstrap procedure (which hard codes some dictionary metadata into row cache before they are available through buffer cache).<\/p>\n\nwith function countrows(con_name varchar2,name varchar2) return number as\n n number; \n saved_con_name varchar2(128);\nbegin\n saved_con_name:=sys_context('userenv','con_name');\n execute immediate 'alter session set container='||con_name;\n execute immediate 'select count(*) from \"'||name||'\"' into n;\n execute immediate 'alter session set container='||saved_con_name;\n return n;\nexception when others then\n execute immediate 'alter session set container='||saved_con_name;\n return null;\nend;\nfunction hex2asc(s varchar2) return varchar2 as r varchar2(32000);\nbegin\n for i in 1..length(s)\/2\n loop\n exit when substr(s,2*i-1,2)='00';\n r:=r||chr(to_number(substr(s,2*i-1,2),'XX'));\n end loop;\n return r;\nend; \nselect v.*\n,(select count(*) from v$rowcache_parent where key like '00%' and hex2asc(substr(key,13)||'00') like object_name||'%' and existent='Y' and con_id=1) rowcache_entries\n,(select substr(sql_text,1,30) from bootstrap$ where sql_text like '%TABLE '||object_name||'(%') bootstrap\nfrom (\nselect object_name,countrows('PDB$SEED',object_name) SEED_COUNT,countrows('CDB$ROOT',object_name) ROOT_COUNT\nfrom user_objects where object_type='TABLE' and oracle_maintained='Y' and object_name like '%$'\n) v \nwhere root_count>0 order by seed_count desc, root_count desc;\n\/\n<\/code><\/pre>\n\nOBJECT_NAME SEED_COUNT ROOT_COUNT ROWCACHE_ENTRIES BOOTSTRAP \n------------------------------ ---------- ---------- --------------------------------------- ------------------------------\nDEPENDENCY$ 162253 162180 2 \nCOL$ 111623 111663 2 CREATE TABLE COL$(\"OBJ#\" NUMBE\nOBJ$ 91511 91721 2 CREATE TABLE OBJ$(\"OBJ#\" NUMBE\nOBJAUTH$ 45085 45084 2 \nHIST_HEAD$ 30585 50516 2 \nACCESS$ 27351 109485 2 \nKOTAD$ 25927 27456 2 \nJAVASNM$ 25073 25073 2 \nHISTGRM$ 22653 72890 2 \nSETTINGS$ 19872 52936 2 \nSOURCE$ 17608 327589 2 \nATTRIBUTE$ 13975 13975 2 \nPARAMETER$ 11785 11785 2 \nCCOL$ 11362 11400 2 CREATE TABLE CCOL$(\"CON#\" NUMB\nCON$ 9648 9686 2 CREATE TABLE CON$(\"OWNER#\" NUM\nCDEF$ 9493 9685 2 CREATE TABLE CDEF$(\"CON#\" NUMB\nMETASCRIPTFILTER$ 7365 7365 2 \nIDL_SB4$ 7288 17787 2 \nICOL$ 6432 6432 2 CREATE TABLE ICOL$(\"OBJ#\" NUMB\nIDL_UB1$ 6290 53505 2 \nIDL_UB2$ 5931 13029 2 \nOID$ 5119 6574 2 \nIND$ 4264 4264 2 CREATE TABLE IND$(\"OBJ#\" NUMBE\n<\/code><\/pre>\n