{"id":10148,"date":"2017-05-26T19:13:39","date_gmt":"2017-05-26T17:13:39","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/"},"modified":"2017-05-26T19:13:39","modified_gmt":"2017-05-26T17:13:39","slug":"12cr2-needs-to-connect-with-password-for-cross-pdb-dml","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/","title":{"rendered":"12cR2 needs to connect with password for Cross-PDB DML"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nIn a previous post<\/a>, I explained that Cross-PDB DML, executing an update\/delete\/insert with the CONTAINERS() clause, seems to be implemented with implicit database links. Connecting through a database link requires a password and this blog post is about an error you may encounter: ORA-01017: invalid username\/password; logon denied<\/p>\n

This blog post also explains a consequence of this implementation, the big inconsistency of CONTAINERS() function because the implementation is completely different for queries (select) and for insert\/delete\/update, and you may finally write and read from different schemas.
\n
\nWe do not need Application Container for Cross-PDB DML and we don’t even need metadata link tables. Just tables with same columns. Here I have a DEMO table which is just a copy of DUAL, and it is created in CDB$ROOT and in PDB1 (CON_ID=3), owned by SYS.<\/p>\n

Implicit database link<\/h3>\n

I’m connecting to CDB$ROOT with user, password and service name:<\/p>\n

\nSQL> connect sys\/oracle@\/\/localhost\/CDB1A as sysdba\nConnected.\n<\/code><\/pre>\n
I insert a row into the DEMO table in the PDB1, which is CON_ID=3:<\/p>\n
\nSQL> insert into containers(DEMO) (con_id,dummy) values (3,'Y');\n1 row created.\n<\/code><\/pre>\n
This works in 12.2, is documented, and is an alternative way to switching to the container.<\/p>\n
But now, let’s try to do the same when connecting with ‘\/ as sysdba’:<\/p>\n
\nSQL> connect \/ as sysdba\nConnected.\nSQL> insert into containers(DEMO) (con_id,dummy) values (3,'Y');\n \ninsert into containers(DEMO) (con_id,dummy) values (3,'Y')\n*\nERROR at line 1:\nORA-01017: invalid username\/password; logon denied\nORA-02063: preceding line from PDB1\n<\/code><\/pre>\n
The first message mentions invalid user\/password, and the second one mentions a database link having the same name as the container.
\nAs I described in the previous post<\/a> the CONTAINERS() opens an implicit database link when doing some modifications to another container. But a database link requires a connection and no user\/password has been provided. It seems that it tries to connect with the same user and password as the one provided to connect to the root.<\/p>\n
Then, I provide the user\/password but with local connection (no service name):<\/p>\n
\nSQL> connect sys\/oracle as sysdba\nConnected.\nSQL> insert into containers(DEMO) (con_id,dummy) values (3,'Y');\ninsert into containers(DEMO) (con_id,dummy) values (3,'Y')\n*\nERROR at line 1:\nORA-01017: invalid username\/password; logon denied\n<\/code><\/pre>\n
There is no mention of a database link here, but still impossible to connect. Then it seems that the session needs our connection string to find out how to connect to the PDB.<\/p>\n
Explicit database link<\/h3>\n
There is an alternative. You can create the database link explicitly and then it will be used by the container(), having all information required password and service. But the risk is that you define this database link to connect to another user. <\/p>\n
Here I have also a DEMO table created in SCOTT:<\/p>\n
\nSQL> create database link PDB1 connect to scott identified by tiger using '\/\/localhost\/PDB1';\nDatabase link created.\n \nSQL> select * from DEMO@PDB1;\n \nD\n-\nX\n<\/code><\/pre>\n
From the root I insert with CONTAINERS() without mentioning the schema:<\/p>\n
\nSQL> insert into containers(DEMO) (con_id,dummy) values (3,'S');\n1 row created.\n<\/code><\/pre>\n
I have no errors here (I’m still connected \/ as sysdba) because I have a database link with the same name as the one it tries to use implicitly. So it works without any error or warning. But my database link does not connect to the same schema (SYS) but to SCOTT. And because a DEMO table was there with same columns, the row was actually inserted into the SCOTT schema:<\/p>\n
\nSQL> select * from DEMO@PDB1;\n \nD\n-\nX\nS\n<\/code><\/pre>\n
The big problem here is that when doing a select through the same CONTAINER() function, a different mechanism is used, not using the database link but session switching to the other container, in same schema, so the row inserted through INSERT INTO CONTAINER() is not displayed by SELECT FROM CONTAINER():<\/p>\n
SQL> select * from containers(DEMO);\n \nD     CON_ID\n- ----------\nX          1\nX          3\nY          3\n<\/code><\/pre>\n
So what?<\/h3>\n
I don’t know if the first problem (invalid user\/password) will be qualified as a bug but I hope the second one will. Cross-PDB DML will be an important component of Application Containers, and having a completely different implementation for SELECT and for INSERT\/UPDATE\/DELETE may be a source of problems. In my opinion, both should use container switch within the same session, but that means that a transaction should be able to write in multiple containers, which is not possible currently.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . In a previous post, I explained that Cross-PDB DML, executing an update\/delete\/insert with the CONTAINERS() clause, seems to be implemented with implicit database links. Connecting through a database link requires a password and this blog post is about an error you may encounter: ORA-01017: invalid username\/password; logon denied This blog post […]<\/p>\n","protected":false},"author":27,"featured_media":10149,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198],"tags":[656,1114,209],"type_dbi":[],"class_list":["post-10148","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-management","tag-12-2","tag-application-containers","tag-oracle-12c"],"acf":[],"yoast_head":"\n12cR2 needs to connect with password for Cross-PDB DML - dbi Blog<\/title>\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\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"12cR2 needs to connect with password for Cross-PDB DML\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . In a previous post, I explained that Cross-PDB DML, executing an update\/delete\/insert with the CONTAINERS() clause, seems to be implemented with implicit database links. Connecting through a database link requires a password and this blog post is about an error you may encounter: ORA-01017: invalid username\/password; logon denied This blog post […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-05-26T17:13:39+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/logopoug.png\" \/>\n\t<meta property=\"og:image:width\" content=\"388\" \/>\n\t<meta property=\"og:image:height\" content=\"197\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\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=\"4 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\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"12cR2 needs to connect with password for Cross-PDB DML\",\"datePublished\":\"2017-05-26T17:13:39+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/\"},\"wordCount\":611,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/logopoug.png\",\"keywords\":[\"12.2\",\"Application Containers\",\"Oracle 12c\"],\"articleSection\":[\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/\",\"name\":\"12cR2 needs to connect with password for Cross-PDB DML - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/logopoug.png\",\"datePublished\":\"2017-05-26T17:13:39+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/logopoug.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/logopoug.png\",\"width\":388,\"height\":197},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"12cR2 needs to connect with password for Cross-PDB DML\"}]},{\"@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":"12cR2 needs to connect with password for Cross-PDB DML - dbi Blog","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\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/","og_locale":"en_US","og_type":"article","og_title":"12cR2 needs to connect with password for Cross-PDB DML","og_description":"By Franck Pachot . In a previous post, I explained that Cross-PDB DML, executing an update\/delete\/insert with the CONTAINERS() clause, seems to be implemented with implicit database links. Connecting through a database link requires a password and this blog post is about an error you may encounter: ORA-01017: invalid username\/password; logon denied This blog post […]","og_url":"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/","og_site_name":"dbi Blog","article_published_time":"2017-05-26T17:13:39+00:00","og_image":[{"width":388,"height":197,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/logopoug.png","type":"image\/png"}],"author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"12cR2 needs to connect with password for Cross-PDB DML","datePublished":"2017-05-26T17:13:39+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/"},"wordCount":611,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/logopoug.png","keywords":["12.2","Application Containers","Oracle 12c"],"articleSection":["Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/","url":"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/","name":"12cR2 needs to connect with password for Cross-PDB DML - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/logopoug.png","datePublished":"2017-05-26T17:13:39+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/logopoug.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/logopoug.png","width":388,"height":197},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-needs-to-connect-with-password-for-cross-pdb-dml\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"12cR2 needs to connect with password for Cross-PDB DML"}]},{"@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\/10148","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=10148"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10148\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/10149"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=10148"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=10148"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=10148"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=10148"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}