Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Milind.

Asked: July 09, 2021 - 3:53 am UTC

Last updated: November 30, 2022 - 1:22 am UTC

Version: 18.3.0

Viewed 1000+ times

You Asked

I have created application root container and two applicatioon PDBs. In application root created a table products ar follows:
CREATE TABLE products SHARING=EXTENDED DATA (prod_id NUMBER(4) CONSTRAINT pk_products PRIMARY KEY, prod_name VARCHAR2(15));
ALTER TABLE hr_app_owner.products ENABLE containers_default;

If containers_default is enabled on this table, then query (select * from products;) hangs. When containers_default is disabled, query works using CONTAINERS clause (select * from CONTAINERS(products);). Is this is the restriction for SHARING=EXTENDED DATA? I tried to search through documentation without any luck.

Thanks in advance.

and Connor said...

Can we get a top to bottom test case? I can't reproduce

SQL> conn / as sysdba
Connected.
SQL>
SQL> create pluggable database conroot as application container admin user root_dba identified by tiger
  2    file_name_convert=('pdbseed','conroot');

Pluggable database created.

SQL>
SQL> alter pluggable database conroot open;

Pluggable database altered.

SQL>
SQL>
SQL> conn / as sysdba
Connected.
SQL> alter session set container = conroot;

Session altered.

SQL> create tablespace appdata datafile 'X:\ORACLE\ORADATA\DB19\CONROOT\APPDATA.DBF' size 100m;

Tablespace created.

SQL>
SQL> create pluggable database conapp admin user app_admin identified by tiger
  2    file_name_convert=('pdbseed','conapp');

Pluggable database created.

SQL>
SQL> alter pluggable database conapp open;

Pluggable database altered.

SQL>
SQL> alter session set container = conapp;

Session altered.

SQL> create tablespace appdata datafile 'X:\ORACLE\ORADATA\DB19\CONAPP\APPDATA.DBF' size 100m;

Tablespace created.

SQL>
SQL> alter pluggable database application all sync;

Pluggable database altered.

SQL> conn / as sysdba
Connected.
SQL> alter session set container = conroot;

Session altered.

SQL>
SQL> alter pluggable database application parent_app begin install '1.0';

Pluggable database altered.

SQL>
SQL> create user parent_app_schema identified by parent_app_schema
  2    default tablespace appdata
  3    quota unlimited on appdata
  4    container=all;

User created.

SQL>
SQL> grant create session, create table to parent_app_schema;

Grant succeeded.

SQL>
SQL> CREATE TABLE parent_app_schema.products SHARING=EXTENDED DATA (prod_id NUMBER(4) CONSTRAINT pk_products PRIMARY KEY, prod_name VARCHAR2(15));

Table created.

SQL> ALTER TABLE parent_app_schema.products ENABLE containers_default;

Table altered.

SQL> insert into parent_app_schema.products values (10,'PRODUCT');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> alter pluggable database application parent_app end install;

Pluggable database altered.

SQL> conn / as sysdba
Connected.
SQL> alter session set container = conapp;

Session altered.

SQL> alter pluggable database application parent_app sync;

Pluggable database altered.

SQL>
SQL> select * from parent_app_schema.products;

   PROD_ID PROD_NAME
---------- ---------------
        10 PRODUCT

1 row selected.

SQL>
SQL> alter session set container = conroot;

Session altered.

SQL> select * from parent_app_schema.products;

   PROD_ID PROD_NAME           CON_ID
---------- --------------- ----------
        10 PRODUCT                  7
        10 PRODUCT                  8

2 rows selected.

SQL>
SQL>
SQL>


But also, check your HOST/PORT settings as per below


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Top to bottom test case

Milind Chaudhari, August 03, 2021 - 3:21 am UTC

Thanks for the reply. Regret that I did not supplied complete test case earlier.
Please find below complete code used by me.

mkdir /u01/app/oracle/oradata/ORCL18C/hr_root
mkdir /u01/app/oracle/oradata/ORCL18C/hr_root/hr_pdb1
mkdir /u01/app/oracle/oradata/ORCL18C/hr_root/hr_pdb2

-- Hostname as well as CDB name is orcl18c
-- Application root name is hr_root
-- Two application PDBs are hr_pdb1 and hr_pdb2
-- products table is created with SHARING=EXTENDED DATA
-- For products table 'ENABLE containers_default' used, so that cross pdb query can be used without containers(products).

-- 1. Create app root
CREATE PLUGGABLE DATABASE hr_root AS APPLICATION CONTAINER
ADMIN USER adminhr IDENTIFIED BY oracle ROLES=(CONNECT)
CREATE_FILE_DEST='/u01/app/oracle/oradata/ORCL18C/hr_root';
ALTER PLUGGABLE DATABASE hr_root OPEN;

-- 2. Install application
conn sys/oracle@orcl18c/hr_root as sysdba

ALTER PLUGGABLE DATABASE APPLICATION hr_app BEGIN INSTALL '1.0';
CREATE TABLESPACE hr_data DATAFILE SIZE 10M AUTOEXTEND ON MAXSIZE 300M;

CREATE USER hr_app_owner IDENTIFIED BY oracle DEFAULT TABLESPACE hr_data QUOTA UNLIMITED ON hr_data CONTAINER=ALL;
GRANT connect, resource, alter database, dba TO hr_app_owner;

ALTER SESSION SET CURRENT_SCHEMA=hr_app_owner; 

CREATE TABLE products SHARING=EXTENDED DATA (prod_id NUMBER(4) CONSTRAINT pk_products PRIMARY KEY, prod_name VARCHAR2(15));
INSERT INTO products VALUES (101, 'TV');
INSERT INTO products VALUES (102, 'Mobile');
COMMIT;

ALTER PLUGGABLE DATABASE APPLICATION hr_app END INSTALL '1.0';

-- 3. Create application pdb hr_pdb1
conn sys/oracle@orcl18c/hr_root as sysdba

CREATE PLUGGABLE DATABASE hr_pdb1 
ADMIN USER adminhr1 IDENTIFIED BY oracle ROLES=(CONNECT)
CREATE_FILE_DEST='/u01/app/oracle/oradata/ORCL18C/hr_root/hr_pdb1';
ALTER PLUGGABLE DATABASE hr_pdb1 OPEN;

conn sys/oracle@orcl18c/hr_pdb1 as sysdba
ALTER PLUGGABLE DATABASE APPLICATION hr_app SYNC;

conn hr_app_owner/oracle@orcl18c/hr_pdb1
INSERT INTO products VALUES (103, 'Calculator');
COMMIT;
SELECT * FROM products;

-- 4. Create application pdb hr_pdb2
conn sys/oracle@orcl18c/hr_root as sysdba

CREATE PLUGGABLE DATABASE hr_pdb2 
ADMIN USER adminhr2 IDENTIFIED BY oracle ROLES=(CONNECT)
CREATE_FILE_DEST='/u01/app/oracle/oradata/ORCL18C/hr_root/hr_pdb2';
ALTER PLUGGABLE DATABASE hr_pdb2 OPEN;

conn sys/oracle@orcl18c/hr_pdb2 as sysdba
ALTER PLUGGABLE DATABASE APPLICATION hr_app SYNC;

conn hr_app_owner/oracle@orcl18c/hr_pdb2
INSERT INTO products VALUES (104, 'Laptop');
COMMIT;
SELECT * FROM products;

-- 5. Cross PDB query
CONNECT sys/oracle@orcl18c/hr_root AS SYSDBA
SELECT * FROM hr_app_owner.products;
ALTER PLUGGABLE DATABASE APPLICATION hr_app BEGIN UPGRADE '1.0' TO '1.1';
ALTER TABLE hr_app_owner.products ENABLE containers_default; -- if enabled, then query on this table hangs. ???
ALTER PLUGGABLE DATABASE APPLICATION hr_app END UPGRADE TO '1.1';

CONNECT sys/oracle@orcl18c/hr_pdb1 AS SYSDBA
ALTER PLUGGABLE DATABASE APPLICATION hr_app SYNC;
CONNECT sys/oracle@orcl18c/hr_pdb2 AS SYSDBA
ALTER PLUGGABLE DATABASE APPLICATION hr_app SYNC;

CONNECT hr_app_owner/oracle@orcl18c/hr_root
SELECT * FROM products; -- Hangs ??? Why?
--- ???? Query hangs after upgrading applications

Run queries on CDB$ROOT from PDB

A reader, November 25, 2022 - 7:13 am UTC


Hi,

We are run queries on CDB from PDB using "alter session set container=CDB$ROOT".

Is there anyway the same can also be done using PL/SQL?

Thank you.


Connor McDonald
November 28, 2022 - 6:00 am UTC

From the whitepaper

https://www.oracle.com/technetwork/database/multitenant/learn-more/multitenant-security-concepts-12c-2402462.pdf

The set container privilege has certain restrictions in PL/SQL. Although developers may think that they can use “execute immediate alter session set container” to switch between two containers inside a pl/sql procedure, function or package it is blocked from execution from within a PDB.

You can create a database link to a target PDB (or CDB), and then run distributed queries in the normal way, ie select * from mytable@my_db_link

Thank you Connor

A reader, November 28, 2022 - 7:09 am UTC

Thank you Connor, will try these out.
Cheers!

PS: For some reason this also got posted in another question ;o)
Connor McDonald
November 30, 2022 - 1:22 am UTC

Double the thanks works for me :-)

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database