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.
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)
November 30, 2022 - 1:22 am UTC
Double the thanks works for me :-)