Back in the days everything was easier, wasn’t it? Thus far you connect to your database and in doubt you send a query to v$instance or v$database to check you’re actually connected to the right place. But since 12c the world is no more that clear. Meanwhile we\u2019ve got the Multitenant Option and by connecting to a Pluggable Database, maybe you won\u2019t get the desired result.<\/p>\n
But for what reason it is such a problem?
In a multitenant environment you can connect directly to your Pluggable Database, but also jump between Pluggable Databases and the Root Container back and forth. When you work with several sessions and also several windows, you can lose track sometimes. Also for scripting it can be helpful to make changes to the right PDB and not to the Root Container by mistake.<\/p>\n
For the following example we work with a simple 12c Multitenant Database with CDB (the ROOT Container) and an attached Pluggable Database (PDB).<\/p>\n
Our v$instance<\/strong> is not very helpful anymore regarding PDB, even the result is completeley correct:<\/p>\n Unfortunately the same thing with v$database<\/strong>:<\/p>\n With v$pdbs<\/strong> we do have a first option to get helpful information. You will get similar results with select * from cdb_pdbs<\/strong>. In the example we jump per alter session set container from CDB to PDB and back:<\/p>\n As you can see, you get informations about a PDB$SEED in the Container Database. The SEED Database is used as a draft in the multitenant environment for creating new PDB\u2019s. In a PDB you will always get only one result in every case, your current PDB.<\/p>\n Pay attention to the DB_NAME. It will always return the name of the (Root) Container Database. You may considered the CON_ID. Don\u2019t be confused – CON_ID 0 means the whole Multitenant Database and 1 is reserved for the Root Container. CON_ID 2 is always PDB$SEED. Everything else from CON_ID 3 up is a Pluggable Databases.<\/p>\n Having that in mind and with a bit of DECODE you get the current database name and if you are connected to a CDB or PDB:<\/p>\n Easier and a bit faster is using SHOW CON_ID, SHOW CON_NAME<\/strong> via SQL Plus. But it only works here and SHOW PDBS<\/strong> only works as SYSDBA. Pay attention to the connect \/ as sysdba \u2013 freshly connecting I change from PDB to CDB. Don\u2019t be confused by the parameter db_name. Concentrate on con_id and con_name:<\/p>\n Starting Oracle 12.2 sys_context(‘USERENV’,’DB_NAME’) will show the name of the Database in CDB$ROOT and the name of the PDB inside the PDB.<\/p>\n","protected":false},"excerpt":{"rendered":" About problems with Oracle 12c Multitenant Database Back in the days everything was easier, wasn’t it? Thus far you connect to your database and in doubt you send a query to v$instance or v$database to check you’re actually connected to the right place. But since 12c the world is no more that clear. Meanwhile we\u2019ve got the Multitenant Option and by connecting to a Pluggable Database, maybe you won\u2019t get the desired result. But for what reason it is such a problem?<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_crdt_document":"","_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[454,406],"tags":[302,306,307,22,218],"class_list":["post-307","post","type-post","status-publish","format-standard","hentry","category-general-en","category-oracle-en","tag-12c-2","tag-cdb-2","tag-multitenant-2","tag-oracle","tag-pdb"],"acf":{"occupation":"Write the Occupation of the Person","person_can-be-speaker":true,"person_is-team":true,"person_related-user-account":null,"person_ordering-number":50,"publication_speakers":null,"publication_content-language":["de"],"publication_files":null,"publication_event":"","publication_date":null},"spectra_custom_meta":{"_fgj2wp_old_id":["307"],"_yoast_wpseo_metadesc":["oracle, CDB, PDB, 12c, Multitenant"],"_yoast_wpseo_metakeywords":["oracle, CDB, PDB, 12c, Multitenant"],"layout_show-author-box":["1"],"_layout_show-author-box":["field_5a64ee9cc6490"],"_edit_lock":["1602076947:3"],"_edit_last":["3"],"_yoast_wpseo_content_score":["90"],"layout_header":["light"],"_layout_header":["field_5a48111185e5f"],"layout_background":[""],"_layout_background":["field_5a481193c0995"],"layout_hide_services":["0"],"_layout_hide_services":["field_5a4811c5c0996"],"layout_show-categories-tags":["1"],"_layout_show-categories-tags":["field_5a64eee4c6491"],"blogpost_related-publications":[""],"_blogpost_related-publications":["field_5a480debdac62"],"blogpost_related-files":[""],"_blogpost_related-files":["field_5a480f377db29"],"_yoast_wpseo_focuskw_text_input":["Multitenant"],"_yoast_wpseo_focuskw":["Multitenant"],"_yoast_wpseo_linkdex":["53"],"_encloseme":["1"],"_last_translation_edit_mode":["native-editor"],"_yoast_wpseo_primary_category":["1"],"rank_math_primary_category":["1"],"rank_math_description":["oracle, CDB, PDB, 12c, Multitenant"],"rank_math_focus_keyword":["Multitenant"],"rank_math_news_sitemap_robots":["index"],"rank_math_robots":["a:1:{i:0;s:5:\"index\";}"],"astra_style_timestamp_css":["1773683694"],"rank_math_internal_links_processed":["1"],"wpil_sync_report3":["1"],"wpil_links_inbound_internal_count":["0"],"wpil_links_inbound_internal_count_data":["eJxLtDKwqq4FAAZPAf4="],"wpil_links_outbound_internal_count":["0"],"wpil_links_outbound_internal_count_data":["eJxLtDKwqq4FAAZPAf4="],"wpil_links_outbound_external_count":["0"],"wpil_links_outbound_external_count_data":["eJxLtDKwqq4FAAZPAf4="],"wpil_sync_report2_time":["2024-08-27T11:47:27+00:00"],"_uagb_previous_block_counts":["a:90:{s:21:\"uagb\/advanced-heading\";i:0;s:15:\"uagb\/blockquote\";i:0;s:12:\"uagb\/buttons\";i:0;s:18:\"uagb\/buttons-child\";i:0;s:19:\"uagb\/call-to-action\";i:0;s:15:\"uagb\/cf7-styler\";i:0;s:11:\"uagb\/column\";i:0;s:12:\"uagb\/columns\";i:0;s:14:\"uagb\/container\";i:0;s:21:\"uagb\/content-timeline\";i:0;s:27:\"uagb\/content-timeline-child\";i:0;s:14:\"uagb\/countdown\";i:0;s:12:\"uagb\/counter\";i:0;s:8:\"uagb\/faq\";i:0;s:14:\"uagb\/faq-child\";i:0;s:10:\"uagb\/forms\";i:0;s:17:\"uagb\/forms-accept\";i:0;s:19:\"uagb\/forms-checkbox\";i:0;s:15:\"uagb\/forms-date\";i:0;s:16:\"uagb\/forms-email\";i:0;s:17:\"uagb\/forms-hidden\";i:0;s:15:\"uagb\/forms-name\";i:0;s:16:\"uagb\/forms-phone\";i:0;s:16:\"uagb\/forms-radio\";i:0;s:17:\"uagb\/forms-select\";i:0;s:19:\"uagb\/forms-textarea\";i:0;s:17:\"uagb\/forms-toggle\";i:0;s:14:\"uagb\/forms-url\";i:0;s:14:\"uagb\/gf-styler\";i:0;s:15:\"uagb\/google-map\";i:0;s:11:\"uagb\/how-to\";i:0;s:16:\"uagb\/how-to-step\";i:0;s:9:\"uagb\/icon\";i:0;s:14:\"uagb\/icon-list\";i:0;s:20:\"uagb\/icon-list-child\";i:0;s:10:\"uagb\/image\";i:0;s:18:\"uagb\/image-gallery\";i:0;s:13:\"uagb\/info-box\";i:0;s:18:\"uagb\/inline-notice\";i:0;s:11:\"uagb\/lottie\";i:0;s:21:\"uagb\/marketing-button\";i:0;s:10:\"uagb\/modal\";i:0;s:18:\"uagb\/popup-builder\";i:0;s:16:\"uagb\/post-button\";i:0;s:18:\"uagb\/post-carousel\";i:0;s:17:\"uagb\/post-excerpt\";i:0;s:14:\"uagb\/post-grid\";i:0;s:15:\"uagb\/post-image\";i:0;s:17:\"uagb\/post-masonry\";i:0;s:14:\"uagb\/post-meta\";i:0;s:18:\"uagb\/post-taxonomy\";i:0;s:18:\"uagb\/post-timeline\";i:0;s:15:\"uagb\/post-title\";i:0;s:20:\"uagb\/restaurant-menu\";i:0;s:26:\"uagb\/restaurant-menu-child\";i:0;s:11:\"uagb\/review\";i:0;s:12:\"uagb\/section\";i:0;s:14:\"uagb\/separator\";i:0;s:11:\"uagb\/slider\";i:0;s:17:\"uagb\/slider-child\";i:0;s:17:\"uagb\/social-share\";i:0;s:23:\"uagb\/social-share-child\";i:0;s:16:\"uagb\/star-rating\";i:0;s:23:\"uagb\/sure-cart-checkout\";i:0;s:22:\"uagb\/sure-cart-product\";i:0;s:15:\"uagb\/sure-forms\";i:0;s:22:\"uagb\/table-of-contents\";i:0;s:9:\"uagb\/tabs\";i:0;s:15:\"uagb\/tabs-child\";i:0;s:18:\"uagb\/taxonomy-list\";i:0;s:9:\"uagb\/team\";i:0;s:16:\"uagb\/testimonial\";i:0;s:14:\"uagb\/wp-search\";i:0;s:19:\"uagb\/instagram-feed\";i:0;s:10:\"uagb\/login\";i:0;s:17:\"uagb\/loop-builder\";i:0;s:18:\"uagb\/loop-category\";i:0;s:20:\"uagb\/loop-pagination\";i:0;s:15:\"uagb\/loop-reset\";i:0;s:16:\"uagb\/loop-search\";i:0;s:14:\"uagb\/loop-sort\";i:0;s:17:\"uagb\/loop-wrapper\";i:0;s:13:\"uagb\/register\";i:0;s:19:\"uagb\/register-email\";i:0;s:24:\"uagb\/register-first-name\";i:0;s:23:\"uagb\/register-last-name\";i:0;s:22:\"uagb\/register-password\";i:0;s:30:\"uagb\/register-reenter-password\";i:0;s:19:\"uagb\/register-terms\";i:0;s:22:\"uagb\/register-username\";i:0;}"],"copied_media_ids":["a:0:{}"],"referenced_media_ids":["a:0:{}"],"_uag_css_file_name":["uag-css-307.css"]},"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"Johannes Ahrends","author_link":"https:\/\/carajandb.com\/en\/author\/9aa6cdb2095bd409\/"},"uagb_comment_info":27,"uagb_excerpt":"About problems with Oracle 12c Multitenant Database Back in the days everything was easier, wasn’t it? Thus far you connect to your database and in doubt you send a query to v$instance or v$database to check you’re actually connected to the right place. But since 12c the world is no more that clear. Meanwhile we\u2019ve…","_links":{"self":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/307","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/comments?post=307"}],"version-history":[{"count":2,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/307\/revisions"}],"predecessor-version":[{"id":7730,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/307\/revisions\/7730"}],"wp:attachment":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/media?parent=307"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/categories?post=307"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/tags?post=307"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}CDB<\/h3>\n
sqlplus system@SJW12C01\n\nSQL> select instance_name, con_id, version from v$instance;\n\nINSTANCE_NAME CON_ID VERSION\n---------------- ---------- -----------------\nsjw12c01 0 12.1.0.2.0\n<\/pre>\n
PDB<\/h3>\n
sqlplus system@SJWPDB01\n\nSQL> select instance_name, con_id, version from v$instance;\n\nINSTANCE_NAME CON_ID VERSION\n---------------- ---------- -----------------\nsjw12c01 0 12.1.0.2.0 \n<\/pre>\n
CDB<\/h3>\n
sqlplus system@SJW12C01\n\nSQL> select name, con_id, db_unique_name from v$database;\n\nNAME CON_ID DB_UNIQUE_NAME\n--------- ---------- ------------------------------\nSJW12C01 0 SJW12C01\n<\/pre>\n
PDB<\/h3>\n
sqlplus system@SJWPDB01\n\nSQL> select name, con_id, db_unique_name from v$database;\n\nNAME CON_ID DB_UNIQUE_NAME\n--------- ---------- ------------------------------\nSJW12C01 0 SJW12C01\n<\/pre>\n
CDB > PDB > CDB<\/h3>\n
sqlplus system@SJW12C01\n\nSQL> select dbid, con_id, name from v$pdbs;\n\n DBID CON_ID NAME\n---------- ---------- ------------------------------\n4043975488 2 PDB$SEED\n4185832567 3 SJWPDB01\n\nSQL> alter session set container = SJWPDB01;\n\nSession altered.\n\nSQL> select dbid, con_id, name from v$pdbs;\n\n DBID CON_ID NAME\n---------- ---------- ------------------------------\n4185832567 3 SJWPDB01\n \nSQL> alter session set container = CDB$ROOT;\n\nSession altered.\n\nSQL> select dbid, con_id, name from v$pdbs;\n\n DBID CON_ID NAME\n---------- ---------- ------------------------------\n4043975488 2 PDB$SEED\n4185832567 3 SJWPDB01\n<\/pre>\n
But there is a better way using USERENV from SYS_CONTEXT:<\/h2>\n
CDB<\/h3>\n
sqlplus system@SJW12C01\n\nSQL> select sys_context('USERENV','CON_NAME') CON_NAME,\n sys_context('USERENV','CON_ID') CON_ID,\n sys_context('USERENV','DB_NAME') DB_NAME from DUAL;\n\nCON_NAME CON_ID DB_NAME\n---------- ---------- ----------\nCDB$ROOT 1 SJW12C01\n<\/pre>\nPDB<\/h3>\n
sqlplus system@SJWPDB01\n\nSQL> select sys_context('USERENV','CON_NAME') CON_NAME,\n sys_context('USERENV','CON_ID') CON_ID,\n sys_context('USERENV','DB_NAME') DB_NAME from DUAL;\n\nCON_NAME CON_ID DB_NAME\n---------- ---------- ----------\nSJWPDB01 3 SJW12C01\n<\/pre>\nCDB<\/h3>\n
sqlplus system@SJW12C01\n\nSQL> select decode(sys_context('USERENV', 'CON_NAME'),'CDB$ROOT',sys_context('USERENV', 'DB_NAME'),sys_context('USERENV', 'CON_NAME')) DB_NAME, \n decode(sys_context('USERENV','CON_ID'),1,'CDB','PDB') TYPE \n from DUAL;\n\nDB_NAME TYPE\n---------- ----\nSJW12C01 CDB\n<\/pre>\nPDB<\/h3>\n
sqlplus system@SJWPDB01\n\nSQL> select decode(sys_context('USERENV', 'CON_NAME'),'CDB$ROOT',sys_context('USERENV', 'DB_NAME'),sys_context('USERENV', 'CON_NAME')) DB_NAME,\n decode(sys_context('USERENV','CON_ID'),1,'CDB','PDB') TYPE \n\t from DUAL;\n\nDB_NAME TYPE\n---------- ----\nSJWPDB01 PDB\n<\/pre>\nCDB > PDB > CDB > PDB<\/h3>\n
sqlplus system@SJW12C01\n\nSQL> show parameter db_name\n\nNAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\ndb_name string SJW12C01\n\nSQL> show con_id\n\nCON_ID\n------------------------------\n1\n\nSQL> show con_name\n\nCON_NAME\n------------------------------\nCDB$ROOT\n\nSQL> alter session set container = SJWPDB01;\n\nSession altered.\n\nSQL> show parameter db_name\n\nNAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\ndb_name string SJW12C01\n\nSQL> show con_id\n\nCON_ID\n------------------------------\n3\n\nSQL> show con_name\n\nCON_NAME\n------------------------------\nSJWPDB01\n\nSQL> conn \/ as sysdba\n\nConnected.\n\nSQL> show pdbs\n\n CON_ID CON_NAME OPEN MODE RESTRICTED\n---------- ------------------------------ ---------- ----------\n 2 PDB$SEED READ ONLY NO\n 3 SJWPDB01 READ WRITE NO\n\nSQL> show con_name\n\nCON_NAME\n------------------------------\nCDB$ROOT\n\nSQL> show con_id\n\nCON_ID\n------------------------------\n1\n\nSQL> alter session set container = SJWPDB01;\n\nSession altered.\n\nSQL> show pdbs\n\n CON_ID CON_NAME OPEN MODE RESTRICTED\n---------- ------------------------------ ---------- ----------\n 3 SJWPDB01 READ WRITE NO\n<\/pre>\n\n\n
Remark:<\/h2>\n\n\n\n