{"id":4104,"date":"2014-11-11T11:41:43","date_gmt":"2014-11-11T10:41:43","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/12102-cdb-views-are-now-using-containers\/"},"modified":"2014-11-11T11:41:43","modified_gmt":"2014-11-11T10:41:43","slug":"12102-cdb-views-are-now-using-containers","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/12102-cdb-views-are-now-using-containers\/","title":{"rendered":"12.1.0.2 CDB views are now using CONTAINERS()"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nI’ve blogged about multitenant internals<\/a> at the time when 12.1.0.1 was just released. Something has changed in 12.1.0.2 and blogging about it was in my todo list for a long time. Now the occasion to do it has been triggered by a question on Oracle Forums about CDB_DATA_FILES not showing PDB$SEED datafiles<\/a> because there is an important change we must be aware of.<\/p>\n

In the previous blog, I have described how you can query PDB information using ‘container data objects’, like the CDB_ views that are created by catcdbviews.sql, defined with the CDB$VIEW clause, which looks like a table function (but is not). It was not documented, and implemented internally with parallel query, partitioning and fixed table. Lot of internal details from Laurent Leturgez here<\/a>.<\/p>\n

12.1.0.2 has introduced the CONTAINERS() table function which does the same but is documented<\/a>.<\/p>\n

And CDB_ views are now using the CONTAINERS() clause instead of CDB$VIEW() one.<\/p>\n

However, there is something different. By default the PDB$SEED is not queried. This comes from the initialization parameter exclude_seed_cdb_view which is true by default:
\nSQL> show parameter exclude_seed_cdb_view
\n 
\nNAME TYPE VALUE
\n------------------------------------ ----------- ------------------------------
\nexclude_seed_cdb_view boolean TRUE
\n 
\nSQL> select * from containers(dual);
\n 
\nD CON_ID
\n- ----------
\nX 3
\nX 1
\n<\/code>
\nYou don’t see PDB$SEED here which is container id 2<\/p>\n

We can get back to the 12.1.0.1 behaviour by setting the parameter to false:
\nSQL> alter session set exclude_seed_cdb_view=false;
\n 
\nSession altered.
\n 
\nSQL> select * from containers(dual);
\n 
\nD CON_ID
\n- ----------
\nX 2
\nX 1
\nX 3
\n<\/code><\/p>\n

So, what’s the point about that? If you are used to list the database datafiles by doing a select from DBA_DATA_FILES then you probably query CDB_DATA_FILES from root in multitenant. And you expect to have all files. You add control files and log file members and you have an exhaustive list of your database files. Maybe you use that in a backup or maintenance script.<\/p>\n

Then if you do that in 12.1.0.2 you will miss the PDB$SEED. Except if you set exclude_seed_cdb_view to false. You can’t rely on CDB_DATA_FILES and that’s the important point raised in the Oracle Forum post.<\/p>\n

I usually prefer to use RMAN for that and RMAN is right:<\/p>\n


\nRMAN> connect target \/
\n 
\nconnected to target database: CDB1 (DBID=836194344)
\n 
\nRMAN> report schema;
\n 
\nusing target database control file instead of recovery catalog
\nReport of database schema for database with db_unique_name CDB1
\n 
\nList of Permanent Datafiles
\n===========================
\nFile Size(MB) Tablespace RB segs Datafile Name
\n---- -------- -------------------- ------- ------------------------
\n1 790 SYSTEM YES \/ZFS01\/oradata\/CDB1\/system01.dbf
\n3 610 SYSAUX NO \/ZFS01\/oradata\/CDB1\/sysaux01.dbf
\n4 160 UNDOTBS1 YES \/ZFS01\/oradata\/CDB1\/undotbs01.dbf
\n5 250 PDB$SEED:SYSTEM NO \/ZFS01\/oradata\/CDB1\/pdbseed\/system01.dbf
\n6 5 USERS NO \/ZFS01\/oradata\/CDB1\/users01.dbf
\n7 490 PDB$SEED:SYSAUX NO \/ZFS01\/oradata\/CDB1\/pdbseed\/sysaux01.dbf
\n8 270 PDB1:SYSTEM NO \/ZFS01\/oradata\/CDB1\/PDB1\/system01.dbf
\n9 530 PDB1:SYSAUX NO \/ZFS01\/oradata\/CDB1\/PDB1\/sysaux01.dbf
\n10 40 PDB1:USERS NO \/ZFS01\/oradata\/CDB1\/PDB1\/SAMPLE_SCHEMA_users01.dbf
\n11 1345 PDB1:EXAMPLE NO \/ZFS01\/oradata\/CDB1\/PDB1\/example01.dbf
\n 
\nList of Temporary Files
\n=======================
\nFile Size(MB) Tablespace Maxsize(MB) Tempfile Name
\n---- -------- -------------------- ----------- --------------------
\n1 275 TEMP 32767 \/ZFS01\/oradata\/CDB1\/temp01.dbf
\n2 20 PDB$SEED:TEMP 32767 \/ZFS01\/oradata\/CDB1\/pdbseed\/pdbseed_temp012014-11-01_08-57-07-AM.dbf
\n3 20 PDB1:TEMP 32767 \/ZFS01\/oradata\/CDB1\/PDB1\/PDB1_temp012014-11-01_09-10-16-AM.dbf
\n<\/code>
\nWhere CDB_DATA_FILES by default shows only:
\nSQL> select con_id,file_id,file_name from cdb_data_files;
\n 
\n CON_ID FILE_ID FILE_NAME
\n---------- ---------- ----------------------------------------------------
\n 1 1 \/ZFS01\/oradata\/CDB1\/system01.dbf
\n 1 3 \/ZFS01\/oradata\/CDB1\/sysaux01.dbf
\n 1 6 \/ZFS01\/oradata\/CDB1\/users01.dbf
\n 1 4 \/ZFS01\/oradata\/CDB1\/undotbs01.dbf
\n 3 8 \/ZFS01\/oradata\/CDB1\/PDB1\/system01.dbf
\n 3 9 \/ZFS01\/oradata\/CDB1\/PDB1\/sysaux01.dbf
\n 3 10 \/ZFS01\/oradata\/CDB1\/PDB1\/SAMPLE_SCHEMA_users01.dbf
\n 3 11 \/ZFS01\/oradata\/CDB1\/PDB1\/example01.dbf
\n<\/code><\/p>\n

More internals…<\/h3>\n

Now what has change about CONTAINERS vs CDB$VIEW?
\n
\nSQL> set autotrace trace explain
\nSQL> select * from cdb_data_files;
\n 
\nExecution Plan
\n----------------------------------------------------------
\nPlan hash value: 1439328272
\n 
\n--------------------------------------------------------------------
\n| Id | Operation | Name | Rows | Pstart| Pstop |
\n--------------------------------------------------------------------
\n| 0 | SELECT STATEMENT | | 20004 | | |
\n| 1 | PX COORDINATOR | | | | |
\n| 2 | PX SEND QC (RANDOM) | :TQ10000 | 20004 | | |
\n| 3 | PX PARTITION LIST ALL| | 20004 | 1 | 254 |
\n| 4 | FIXED TABLE FULL | X$CDBVW$ | 20004 | | |
\n--------------------------------------------------------------------
\n<\/code>
\nIt’s still using parallel processes on a partitioned fixed table
\n
\nSQL> alter session set \"_px_cdb_view_enabled\"=FALSE;
\n 
\nSession altered.
\n 
\nSQL> select * from cdb_data_files;
\n 
\nExecution Plan
\n----------------------------------------------------------
\nPlan hash value: 2351439557
\n 
\n---------------------------------------------------------------
\n| Id | Operation | Name | Rows | Pstart| Pstop |
\n---------------------------------------------------------------
\n| 0 | SELECT STATEMENT | | 20004 | | |
\n| 1 | PARTITION LIST ALL| | 20004 | 1 | 254 |
\n| 2 | FIXED TABLE FULL | X$CDBVW$ | 20004 | | |
\n---------------------------------------------------------------
\n 
\nSQL> alter session set \"_partition_cdb_view_enabled\"=FALSE;
\n 
\nSession altered.
\n 
\nSQL> select * from cdb_data_files;
\n 
\nExecution Plan
\n----------------------------------------------------------
\nPlan hash value: 1784620524
\n 
\n-----------------------------------------------------
\n| Id | Operation | Name | Rows | Bytes |
\n-----------------------------------------------------
\n| 0 | SELECT STATEMENT | | 20004 | 8185K|
\n| 1 | FIXED TABLE FULL| X$CDBVW$ | 20004 | 8185K|
\n-----------------------------------------------------
\n<\/code>
\nAnd documented parameters _px_cdb_view_enabled and _partition_cdb_view_enabled still control it.<\/p>\n

Each partition is estimated to return 10000 rows (that’s hardcoded) so the estimation for 2 partitions (CDB$ROOT and PDB1) is about 20000.<\/p>\n

However, that was after I tried to gather statistics for cdb views:
\n
\nSQL> select dbms_pdb.update_cdbvw_stats from dual;
\n<\/code>
\nand I got statistics:
\n
\nSQL> select * from cdbvw_stats$ where objname='DBA_DATA_FILES';
\n 
\nOBJNAME TIMESTAMP FLAGS ROWCNT SPARE1
\n------------------------------ --------- ---------- ---------- ----------
\nDBA_DATA_FILES 11-NOV-14 1 4
\n<\/code>
\nwhich are the number of datafiles in my CDB$ROOT. So I expected the estimation to be about 8. But that will probably be for another blog post…<\/p>\n

What is important to know for the moment is that by default CDB_DATA_FILES don’t show all your database files.<\/p>\n","protected":false},"excerpt":{"rendered":"

By Franck Pachot . I’ve blogged about multitenant internals at the time when 12.1.0.1 was just released. Something has changed in 12.1.0.2 and blogging about it was in my todo list for a long time. Now the occasion to do it has been triggered by a question on Oracle Forums about CDB_DATA_FILES not showing PDB$SEED […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198,59],"tags":[220,64,320,96,209,66,223],"type_dbi":[],"class_list":["post-4104","post","type-post","status-publish","format-standard","hentry","category-database-management","category-oracle","tag-cdb","tag-multitenant","tag-multitenant-database","tag-oracle","tag-oracle-12c","tag-pdb","tag-pluggable-databases"],"acf":[],"yoast_head":"\n12.1.0.2 CDB views are now using CONTAINERS() - dbi Blog<\/title>\n<meta name=\"description\" content=\"CDB views don't show PDB$SEED information by default. That can be misleading for CDB_DATA_FILES.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/12102-cdb-views-are-now-using-containers\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"12.1.0.2 CDB views are now using CONTAINERS()\" \/>\n<meta property=\"og:description\" content=\"CDB views don't show PDB$SEED information by default. That can be misleading for CDB_DATA_FILES.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/12102-cdb-views-are-now-using-containers\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2014-11-11T10:41:43+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/12102-cdb-views-are-now-using-containers\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/12102-cdb-views-are-now-using-containers\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"12.1.0.2 CDB views are now using CONTAINERS()\",\"datePublished\":\"2014-11-11T10:41:43+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/12102-cdb-views-are-now-using-containers\\\/\"},\"wordCount\":472,\"commentCount\":0,\"keywords\":[\"CDB\",\"multitenant\",\"Multitenant database\",\"Oracle\",\"Oracle 12c\",\"PDB\",\"Pluggable Databases\"],\"articleSection\":[\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/12102-cdb-views-are-now-using-containers\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/12102-cdb-views-are-now-using-containers\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/12102-cdb-views-are-now-using-containers\\\/\",\"name\":\"12.1.0.2 CDB views are now using CONTAINERS() - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2014-11-11T10:41:43+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"CDB views don't show PDB$SEED information by default. That can be misleading for CDB_DATA_FILES.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/12102-cdb-views-are-now-using-containers\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/12102-cdb-views-are-now-using-containers\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/12102-cdb-views-are-now-using-containers\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"12.1.0.2 CDB views are now using CONTAINERS()\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/oracle-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"12.1.0.2 CDB views are now using CONTAINERS() - dbi Blog","description":"CDB views don't show PDB$SEED information by default. That can be misleading for CDB_DATA_FILES.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/12102-cdb-views-are-now-using-containers\/","og_locale":"en_US","og_type":"article","og_title":"12.1.0.2 CDB views are now using CONTAINERS()","og_description":"CDB views don't show PDB$SEED information by default. That can be misleading for CDB_DATA_FILES.","og_url":"https:\/\/www.dbi-services.com\/blog\/12102-cdb-views-are-now-using-containers\/","og_site_name":"dbi Blog","article_published_time":"2014-11-11T10:41:43+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/12102-cdb-views-are-now-using-containers\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/12102-cdb-views-are-now-using-containers\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"12.1.0.2 CDB views are now using CONTAINERS()","datePublished":"2014-11-11T10:41:43+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/12102-cdb-views-are-now-using-containers\/"},"wordCount":472,"commentCount":0,"keywords":["CDB","multitenant","Multitenant database","Oracle","Oracle 12c","PDB","Pluggable Databases"],"articleSection":["Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/12102-cdb-views-are-now-using-containers\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/12102-cdb-views-are-now-using-containers\/","url":"https:\/\/www.dbi-services.com\/blog\/12102-cdb-views-are-now-using-containers\/","name":"12.1.0.2 CDB views are now using CONTAINERS() - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2014-11-11T10:41:43+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"CDB views don't show PDB$SEED information by default. That can be misleading for CDB_DATA_FILES.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/12102-cdb-views-are-now-using-containers\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/12102-cdb-views-are-now-using-containers\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/12102-cdb-views-are-now-using-containers\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"12.1.0.2 CDB views are now using CONTAINERS()"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4104","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=4104"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4104\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4104"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4104"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4104"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4104"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}