I think there is a bug with extended data-linked table in Application PDB.
SQL> -- Connect
SQL> connect / as sysdba
Connected.
SQL> -- Version
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0
SQL> -- For the test case, I use OMF
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
------------------- ------ -------------------
db_create_file_dest string /opt/oracle/oradata
SQL> -- Create and open an Application Root PDB
SQL> show con_id
CON_ID
------------------------------
1
SQL> 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 PDB
SQL> alter session set container = pdb_app;
Session altered.
SQL> -- Install application with one tablespace, one user and one table with sharing extended data
SQL> 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 PDB
SQL> 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 application
SQL> 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 table
SQL> select * from app.t1;
X
----------
0
SQL> insert into app.t1 values(1);
1 row inserted.
SQL> select * from app.t1;
X
----------
0
SQL> --> the new line is not visible IN THE SESSION before commit
SQL> commit;
Commit complete.
SQL> select * from app.t1;
X
----------
0
1
SQL> --> the new line is visible IN THE SESSION only after commit
/*
-- SQL script for easy copy-paste
-- Connect
connect / as sysdba
-- Version
select * from v$version;
-- For the test case, I use OMF
show parameter db_create_file_dest
-- Create and open an Application Root PDB
show con_id
create pluggable database pdb_app as application container admin user admin identified by oracle;
alter pluggable database pdb_app open;
-- Connect to Application Root PDB
alter session set container = pdb_app;
-- Install application with one tablespace, one user and one table with sharing extended data
alter 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 PDB
create pluggable database pdb_app1 admin user admin identified by oracle;
alter pluggable database pdb_app1 open;
-- Connect to Application PDB and synchronize application
alter session set container = pdb_app1;
alter pluggable database application MyApp sync;
-- Test insert into extended data table
select * from app.t1;
insert into app.t1 values(1);
select * from app.t1;
--> the new line is not visible IN THE SESSION before commit
commit;
select * from app.t1;
--> the new line is visible IN THE SESSION only after commit
*/
Comments, ideas and advices are welcome.