Forum Stats

  • 3,855,570 Users
  • 2,264,523 Discussions
  • 7,906,069 Comments

Discussions

Issue with extended data-linked table in Application PDB ?

Olivier Heurtel
Olivier Heurtel Member Posts: 4 Blue Ribbon
edited Mar 9, 2018 5:33PM in Multitenant

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.

Answers

This discussion has been closed.