Forum Stats

  • 3,872,090 Users
  • 2,266,378 Discussions
  • 7,911,047 Comments

Discussions

Update activity on a Application container , partitioning table

Arturo Gutierrez
Arturo Gutierrez Member Posts: 339 Bronze Badge
edited Oct 26, 2017 9:07PM in Multitenant

Hello,

Testing the ability to define a PDB partitioning using the container map, I see that work fine for select statements, but not for Updates.

Let me show:

I've the emp table partitioned across 3 PDBS:

SQL> select empno, deptno , con_id from emp;

EMPNO DEPTNO CON_ID
----- ------ ------
7782     10     16
7839     10     16
7934     10     16
7369     20     17
7566     20     17
7788     20     17
7876     20     17
7902     20     17
7499     30     15
7521     30     15
7654     30     15
7698     30     15
7844     30     15
7900     30     15

14 rows selected.

Now, If I use the partitiong key to filter:

SQL>  select empno, deptno , con_id from emp where deptno=10;

EMPNO DEPTNO CON_ID
----- ------ ------
7782     10     16
7839     10     16
7934     10     16

This work fine.

However If I try :

SQL> update emp set sal=sal*10 where deptno=10;

0 rows updated. ---> No Rows updated,

To do this, I need indicate CONTAINERS CLAUSE:

SQL> update containers(emp) set sal=sal*1.10 where con_id = 16;

3 rows updated.

Same, happen with delete.

Also, I try to update the partitioning key to see if the rows move to another PDB.

update  containers(emp) set deptno=10 where  con_id=17

*

ERROR at line 1:

ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found

ORA-02063: preceding line from RESEARCH

Is this a restricction of this reléase, or I need set any parameter to allow update operations?

Thanks

Arturo

Answers

  • Markus Flechtner
    Markus Flechtner Member Posts: 503 Bronze Trophy
    edited Oct 24, 2017 6:44AM

    It seems that you hit bug 21955394 - ORA-2291 in Multitenant Application PDB when foreign key refers to the primary key in Extended Data Link object.

    Patches are available on My Oracle Support.

    HTH

    Markus

  • Arturo Gutierrez
    Arturo Gutierrez Member Posts: 339 Bronze Badge
    edited Oct 24, 2017 8:19AM

    Hi Markus,

    Thanks for the info.

    However, the bug is when you créate in the App root, tables with different sharing options:

    Create table ... sharing=object

    Create table .. sharing=metadata.

    In my case, the tables are created same with sharing=METADATA.

    Thanks

    Arturo

  • Unknown
    edited Oct 24, 2017 12:11PM
    However, the bug is when you créate in the App root, tables with different sharing options:

    So? How is Markus, or anyone, supposed to know what 'sharing options' you used? You haven't even posted your full DB version.

    That is why you need to SHOW US, not just tell us:

    1. WHAT you did

    2. HOW you did it

    3. WHAT results you get

    4. WHAT results you expected to get

    Post ALL INFO needed to try to reproduce the problem.

  • vanpupi
    vanpupi Member Posts: 48
    edited Oct 25, 2017 5:03AM

    Hello,

    Can you provide the sql script you use to reproduce the issue and also the full database version you are using.

    If you provide all steps necessary to reproduce the issue, then it's easier to understand what happens for what reason.

    best regards,

    Pieter

  • Arturo Gutierrez
    Arturo Gutierrez Member Posts: 339 Bronze Badge
    edited Oct 25, 2017 7:33AM

    Hi,

    My Oracle versión is 12.2.0.1, first versión to support Application Containers as you know.

    The enviorement to this situation is:

    1. I have created a Root Container

    2. I have créate a application container

    3. Under this App container I créate 3 PDBs (EMEA, ASIA, USA)

    In the App Root container I créate a user with some tables:

    CREATE TABLE scott.DEPT sharing=METADATA

      (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY
    KEY,

            DNAME VARCHAR2(14) ,

            LOC VARCHAR2(13) ) ;

    CREATE TABLE scott.EMP sharing=METADATA

      (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY
    KEY,

            ENAME VARCHAR2(10),

            JOB VARCHAR2(9),

            MGR NUMBER(4),

            HIREDATE DATE,

            SAL NUMBER(7,2),

            COMM NUMBER(7,2),

            DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES SCOTT.DEPT);

    I executed the commands:

    ALTER TABLE scott.dept ENABLE container_map; and

    ALTER TABLE scott.emp ENABLE containers_default;

    And also the mappping table:

    CREATE TABLE scott.map1

    (deptno number, name varchar2(30))

    PARTITION BY LIST (deptno)

    ( PARTITION EMEA VALUES (10),

    PARTITION ASIA VALUES (20),

    PARTITION USA  VALUES (30));

    ALTER DATABASE SET container_map='scott.map1';

    Now from the App root PDB can I Access to the entire table that is partitioned across 3 PDBs.

    After that, I've inserted some date in every PDB:

    alter sesión set container=EMEA;

    INSERT INTO DEPT VALUES (10,'ACCOUNTING','MADRID');

    ....

    alter sesión set container=USA;

    INSERT INTO DEPT VALUES (20,'HR','NEW YORK');

    Now from the App root container, I can Access to the entire DEPT table that is partitioned across 3 PDBS.

    SQL> SELECT deptno, dname, loc

    FROM scott.dept where deptno = 20; 

    So the access in query mode works correctly, but when it comes to making updates, it does not seem like.

    If you try update some data:

    update dept set deptno=20 where deptno=10;

    The system response is:

    0 rows updated.

    So my question is if the modifications are not supported make them from the root app and you have to do them from the PDB that the information belongs to.
    And also, if it is supported the modification of the partitioning column, involving the movement of the row of a PDB to another.

    Thanks

    Arturo

  • Unknown
    edited Oct 26, 2017 9:07PM

    Thanks for TRYING to provide info. I can appreciate that it takes time, and isn't easy to deal with such a complex use case.

    But, alas, what you posted is not complete

    Post ALL INFO needed to try to reproduce the problem.

    We can't try to reproduce the problem without having the same statements to execute that you used.

    The enviorement to this situation is:1. I have created a Root Container2. I have créate a application container3. Under this App container I créate 3 PDBs (EMEA, ASIA, USA)In the App Root container I créate a user with some tables:

    While those statements attempt to tell us WHAT you did they don't tell us HOW you did it. So we can't be sure that what we do is the same as what you did.

    So all I can offer if a couple of observations about what you did post. It is up to you to determine if they are significant for your use case.

    1. CREATE TABLE scott.map1

    (deptno number, name varchar2(30))

    Ok - except that is NOT the exact definition of 'deptno' you used in the two tables you created. For them you used NUMBER(2).

    Significant? Possibly - probably not. I don't know since I don't have all of the DDL to try to recreate everything you did.

    ALTER TABLE scott.dept ENABLE container_map; andALTER TABLE scott.emp ENABLE containers_default; 

    That one is definitely significant. Did you spot it?

    You enabled the container_map on the DEPT table but enabled containers_default on the EMP table.

    Since your test uses the DEPT table you need to do that alter on DEPT.

    I ALWAYS recommend that people actually try the EXACT examples provided in the documentation. This is especially important for functionality that is both new and complex.

    https://docs.oracle.com/database/122/ADMIN/administering-application-containers-with-sql-plus.htm#ADMIN-GUID-A4C8768F-00…

    Because the reality is that for new technology only Oracle really knows how something is SUPPOSED to work.

    Try fixing those two problems and see if that helps. But if you need more than that you really need to post ALL of the DDL/DML needed to try to reproduce what you are doing.

This discussion has been closed.