4 Replies Latest reply on May 14, 2019 12:09 PM by vibha shrivastava-Oracle

    Table in app container with share=metadata shares data with PDB

    vibha shrivastava-Oracle

      I am trying to see different sharing mode with application container. Working on Oracle 18c. I am working on a small test case where I created a table with share=metadata and inserted a record in this. When I sync this application with PDB and select from this table - I expect to see no data.  But I can see the record inserted in the app container. I can even update it, then how is this mode different from extended data sharing mode ? Also all the document suggest that only the meta data should be shared ? then why app container data should be shared with pdb ? Here is my testcase.

       

      CREATE PLUGGABLE DATABASE appcon2 AS APPLICATION CONTAINER ADMIN USER app_admin IDENTIFIED BY Password1;

      ALTER PLUGGABLE DATABASE appcon2 OPEN;

      ALTER SESSION SET container = appcon2

       

      CREATE PLUGGABLE DATABASE apppdb2 ADMIN USER pdb_admin IDENTIFIED BY Password1;

      ALTER PLUGGABLE DATABASE apppdb2 OPEN;

       

      ----      Create an application tst_app in application container app_root

      ALTER PLUGGABLE DATABASE APPLICATION tst_app BEGIN INSTALL '1.0';

      --- Create common application user tst_app_user1

       

      CREATE USER tst_app_user1 IDENTIFIED BY oracle;

      GRANT CREATE SESSION, create procedure, CREATE TABLE,unlimited tablespace TO tst_app_user1;

       

      CREATE TABLE tst_app_user1.customers SHARING=METADATA

      ( cust_id    NUMBER constraint cust_pk primary key,

           cust_name  varchar2(30),

           cust_add   varchar2(30)

      );

      insert into tst_app_user1.customers  values ('0', 'Root Customer0', 'Chicago');

      commit;

      ALTER PLUGGABLE DATABASE APPLICATION tst_app END INSTALL '1.0';

       

      Usecase 1

      =================

      ALTER SESSION SET container = apppdb2;

      alter pluggable database application tst_app sync

       

      select * from tst_app_user1.customers;

       

      ----I do not expect anything but following record appears

       

         CUST_ID CUST_NAME                  CUST_ADD          

      ---------- ------------------------------ --------------

        0         Root Customer0             Chicago

       

      insert into tst_app_user1.customers  values ('1', 'pdB1 Customer0', 'California');

      commit;

       

      SELECT * FROM tst_app_user1.customers

       

       

      0 Root Customer0 Chicago

      1 pdB1 Customer0 California

       

      Can someone please explain what am I missing

        • 1. Re: Table in app container with share=metadata shares data with PDB
          Markus Flechtner

          Hi,

           

          concerning the different sharing attributes:

           

           

          1. SHARING=METADATA

          - Table is defined in the application root

          - Table is available in all application PDBs.

          - in each application PDB data can be inserted. This data is not visible in other Application PDBs.

           

          2. SHARING=DATA

          - Table is defined in the application root

          - Data is inserted in the application root

          - Table (including data) is available in all application PDBs.

          - Data is read only in the application PDBs

           

          3. SHARING=EXTENDED DATA

          - Table is defined in the application root

          - Data is inserted in the application root

          - Table (including data) is available in all application PDBs.

          - Data which is inserted in the Application root is read only in the application PDBs

          - Additional data can be inserted on Application PDB level

          - This additional data is not available in other PDBs.

           

           

          HTH

          Markus

          • 2. Re: Table in app container with share=metadata shares data with PDB
            vibha shrivastava-Oracle

            Hi Markus,

             

            Thanks, My question is on the first point.

             

            1. SHARING=METADATA

            - Table is defined in the application root

            - Table is available in all application PDBs.

            - in each application PDB data can be inserted. This data is not visible in other Application PDBs.

             

            I am finding these additional features for this SHARING mode

            - Data is inserted in the application root

            - Table (including data) is available in all application PDBs.

            - Data which is inserted in the Application root is Modifiable  in the application PDBs

             

            As per documentation data for SHARING=METADATA is private to each container. So data of application root should not appear in application pdb.

             

            Thanks

            Vibha

            • 3. Re: Table in app container with share=metadata shares data with PDB
              Bhavesh Tinna-Oracle

              Hi Vibha,

               

              The insert statement is reflected in Application PDB as the insert statement is part of INSTALL/UPGRADE process. Ideally inserts to the METADATA shared table will be part of application and will not be in INSTALL/UPGRADE process, unless we want to specifically propagate some data to each application pdb. You can test to insert into the table outside of INSTALL process then the same will not be reflected to the Application PDBs.

               

              Regards

              Bhavesh.

              • 4. Re: Table in app container with share=metadata shares data with PDB
                vibha shrivastava-Oracle

                Thanks Bhavesh.

                 

                Yes, if we do not create record in the app install it works as expected..

                But if we do then this becomes like a 4th way of sharing data where it is very similar to sharing = extended except that record created can be modified and deleted by application PDB.

                I do not find any documentation regarding this. Its confusing.