Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Issue with extended data-linked table in Application PDB ?

Olivier HeurtelMar 7 2018 — edited Mar 9 2018

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

Here is the test case :

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.

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 6 2018
Added on Mar 7 2018
2 comments
402 views