Issue with extended data-linked table in Application PDB ?

Olivier Heurtel

I think there is a bug with extended data-linked table in Application PDB.

Here is the test case :

SQL> -- ConnectSQL> connect / as sysdbaConnected.SQL> -- VersionSQL> select * from v$version;BANNER                                                                               CON_ID-------------------------------------------------------------------------------- ----------Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0PL/SQL Release 12.2.0.1.0 - Production                                                    0CORE    12.2.0.1.0      Production                                                                  0TNS for Linux: Version 12.2.0.1.0 - Production                                            0NLSRTL Version 12.2.0.1.0 - Production                                                    0SQL> -- For the test case, I use OMFSQL> show parameter db_create_file_destNAME                TYPE   VALUE------------------- ------ -------------------db_create_file_dest string /opt/oracle/oradataSQL> -- Create and open an Application Root PDBSQL> show con_idCON_ID------------------------------1SQL> create pluggable database pdb_app as application container admin user admin identified by oracle;Pluggable database PDB_APP created.SQL> alter pluggable database pdb_app open;Pluggable database PDB_APP altered.SQL> -- Connect to Application Root PDBSQL> alter session set container = pdb_app;Session altered.SQL> -- Install application with one tablespace, one user and one table with sharing extended dataSQL> alter pluggable database application MyApp begin install '1.0';Pluggable database APPLICATION altered.SQL> create tablespace app_tbs;Tablespace APP_TBS created.SQL> create user app identified by oracle default tablespace app_tbs quota unlimited on app_tbs;User APP created.SQL> grant connect, resource to app;Grant succeeded.SQL> create table app.t1 sharing = extended data (x number);Table APP.T1 created.SQL> insert into app.t1 values(0);1 row inserted.SQL> commit;Commit complete.SQL> alter pluggable database application MyApp end install;Pluggable database APPLICATION altered.SQL> -- Create and open an Application PDBSQL> create pluggable database pdb_app1 admin user admin identified by oracle;Pluggable database PDB_APP1 created.SQL> alter pluggable database pdb_app1 open;Pluggable database PDB_APP1 altered.SQL> -- Connect to Application PDB and synchronize applicationSQL> alter session set container = pdb_app1;Session altered.SQL> alter pluggable database application MyApp sync;Pluggable database APPLICATION altered.SQL> -- Test insert into extended data tableSQL> select * from app.t1;         X----------         0SQL> insert into app.t1 values(1);1 row inserted.SQL> select * from app.t1;         X----------         0SQL> --> the new line is not visible IN THE SESSION before commitSQL> commit;Commit complete.SQL> select * from app.t1;         X----------         0         1SQL> --> the new line is visible IN THE SESSION only after commit/*-- SQL script for easy copy-paste-- Connectconnect / as sysdba-- Versionselect * from v$version;-- For the test case, I use OMFshow parameter db_create_file_dest-- Create and open an Application Root PDBshow con_idcreate pluggable database pdb_app as application container admin user admin identified by oracle;alter pluggable database pdb_app open;-- Connect to Application Root PDBalter session set container = pdb_app;-- Install application with one tablespace, one user and one table with sharing extended dataalter pluggable database application MyApp begin install '1.0';create tablespace app_tbs;create user app identified by oracle default tablespace app_tbs quota unlimited on app_tbs;grant connect, resource to app;create table app.t1 sharing = extended data (x number);insert into app.t1 values(0);commit;alter pluggable database application MyApp end install ;-- Create and open an Application PDBcreate pluggable database pdb_app1 admin user admin identified by oracle;alter pluggable database pdb_app1 open;-- Connect to Application PDB and synchronize applicationalter session set container = pdb_app1;alter pluggable database application MyApp sync;-- Test insert into extended data tableselect * from app.t1;insert into app.t1 values(1);select * from app.t1;--> the new line is not visible IN THE SESSION before commitcommit;select * from app.t1;--> the new line is visible IN THE SESSION only after commit*/

Comments, ideas and advices are welcome.

Unknown

I won't have time to test for a few hours but one possibly critical mistake in the above.

Your END INSTALL statement did NOT specify the version number.

Per the docs

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/administering-application-containers-with-sql-plus…

Note:Ensure that the application_name and application_version_number match in the ALTER PLUGGABLE DATABASE APPLICATION BEGIN INSTALL statement and theALTER PLUGGABLE DATABASE APPLICATION END INSTALL statement.

So your 'install' may not have completed successfully.

I will look at this again after my commute. But that is such a potentially 'fatal' issue that I wouldn't even try troubleshooting what you have now.

I suggest you start over and follow the steps in the doc 'to the letter'. Problem with new functionality is that no one Oracle knows how it is supposed to work or what effect a 'seemingly' minor omission like version number might have. It is easy to think that Oracle must know what app version the 'END INSTALL' must refer to.

But experience tells me NOT to make assumptions like that.

Franck Pachot

Hi,

You are connected as a common user and you insert cross-container. Currently, this is implemented through database links, thus with another transaction.

See: https://blog.dbi-services.com/12cr2-cross-container-dml-insert-into-container/

Regards,

Franck.