Forum Stats

  • 3,824,937 Users
  • 2,260,441 Discussions
  • 7,896,354 Comments

Discussions

Update a table in ALL PDBs

User_A7RKT
User_A7RKT Member Posts: 223 Blue Ribbon
edited Jun 11, 2017 12:48PM in Multitenant

Hi guys,

It is about Oracle 12c R2 Multitenant option.

I understand that I can use the CONTAINERS clause in an UPDATE statement to update a table in a specific container using WHERE CON_ID=<con_id>... and if you omit this where condition, it will update the table in the current container.

SQL> show user

USER is "C##USER1"

SQL>  update containers(app_users) set user_name='updated'  where con_id=3;

1 row updated.

My question is: is there a way to update a table in ALL the pdbs?

Thanks in advance!

AndrewSayerSUPRIYO DEY

Best Answer

«13

Answers

  • SUPRIYO DEY
    SUPRIYO DEY Member Posts: 2,127 Silver Trophy
    edited May 10, 2017 2:38AM Answer ✓
  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited May 10, 2017 2:59AM
    2729747 wrote:Hi guys,It is about Oracle 12c R2 Multitenant option.I understand that I can use the CONTAINERS clause in an UPDATE statement to update a table in a specific container using WHERE CON_ID=<con_id>... and if you omit this where condition, it will update the table in the current container.SQL> show userUSER is "C##USER1"SQL> update containers(app_users) set user_name='updated' where con_id=3;1 row updated.My question is: is there a way to update a table in ALL the pdbs?Thanks in advance!

    Not knowing if it's possible or not (because I havent searched the documentation yet), I would just try it in my test environment. The obvious guess would be to remove the filter on con_id, so what happens when you try it? I would expect it to either work or fail with an error message stating it can't be done. 

  • User_A7RKT
    User_A7RKT Member Posts: 223 Blue Ribbon
    edited May 10, 2017 3:03AM
    Not knowing if it's possible or not (because I havent searched the documentation yet), I would just try it in my test environment. The obvious guess would be to remove the filter on con_id, so what happens when you try it? I would expect it to either work or fail with an error message stating it can't be done. 
    Re: Update a table in ALL PDBs

    As I said in the question: "if you omit this where condition, it will update the table in the current container"
  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited May 10, 2017 3:46AM

    Sorry my mistake, interesting. I can't see why that would be the case. Does it really let you update a different container when you use the con_id filter? Can you use an inlist or a nonequality (e.g. con_id>=0)

  • User_A7RKT
    User_A7RKT Member Posts: 223 Blue Ribbon
    edited May 10, 2017 3:52AM

    Only equality worked with me :-)

    SQL> update containers(app_users) set user_name='updated'  where con_id>3;

    update containers(app_users) set user_name='updated'  where con_id>3

                    *

    ERROR at line 1:

    ORA-65320: value of CON_ID is not known for DML on CONTAINERS()

    SQL> update containers(app_users) set user_name='updated'  where con_id in (3,4);

    update containers(app_users) set user_name='updated'  where con_id in (3,4)

                      *

    ERROR at line 1:

    ORA-65319: DML on CONTAINERS() specified an unsupported CON_ID predicate

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited May 10, 2017 4:00AM
    2729747 wrote:Only equality worked with me :-)SQL> update containers(app_users) set user_name='updated' where con_id>3;update containers(app_users) set user_name='updated' where con_id>3 *ERROR at line 1:ORA-65320: value of CON_ID is not known for DML on CONTAINERS()SQL> update containers(app_users) set user_name='updated' where con_id in (3,4);update containers(app_users) set user_name='updated' where con_id in (3,4) *ERROR at line 1:ORA-65319: DML on CONTAINERS() specified an unsupported CON_ID predicate

    In that case what about a plsql loop around each possible con_id from v$pdbs using your basic con_id equality filter each time?

    This will start a distributed transaction but I don't think that should cause any real problem.

  • User_A7RKT
    User_A7RKT Member Posts: 223 Blue Ribbon
    edited May 10, 2017 4:02AM

    Workaround is there... but I'm just asking about the concept.

    Thanks!

    AndrewSayer
  • User_A7RKT
    User_A7RKT Member Posts: 223 Blue Ribbon
    edited May 10, 2017 6:38AM

    Actually this behavior makes sense... because a DML transactions cannot span multiple PDBs.

  • Unknown
    edited May 10, 2017 8:33PM
    Actually this behavior makes sense... because a DML transactions cannot span multiple PDBs.

    Yes they can - you ask about 12.2 and the concept of 'application container' is new functionality for 12.2.

  • Franck Pachot
    Franck Pachot Member Posts: 912 Bronze Trophy
    edited May 10, 2017 4:30PM

    Hi,

    >> My question is: is there a way to update a table in ALL the pdbs?

    If 'all the PDBs' means 'all the application PDBs from the application root', then yes. Without a CON_ID the DML updates the default target which is by default all application PDBs.

    SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='CONTAINERS_DEFAULT_TARGET'; 

    Doc: https://docs.oracle.com/database/122/ADMIN/administering-application-containers-with-sql-plus.htm#ADMIN-GUID-02A30F9F-FF…

    This is also in the 12cR2 Multitenant book

    Regards,

    Franck.

This discussion has been closed.