Query Data Across Multiple PDBs with the Oracle Database CONTAINERS Clause

Hidden in plain sight inside Oracle Database is an often-unused, sometimes-undiscovered feature: Cross-PDB Query. With Oracle Multitenant, the CONTAINERS clause allows a properly configured common user to query data across multiple Pluggable Databases from the Container Database root. That can be very handy for demos, reporting, validation checks, migration validation, or operational queries where the same logical object exists in more than one PDB.

In this post, I walk through a demo that allows a common CDB user connected to CDB$ROOT to query local data from two PDBs by using the CONTAINERS clause.

The important lesson is not just how to make the query work. The more interesting part is the security behavior:

GRANT SELECT ON some_root_view TO c##some_user;

is not always enough.

A common user may have object privileges on the root view and still return no rows until the user’s CONTAINER_DATA attribute allows visibility into the target PDBs.

Demo goal

For this demo, I use two PDBs:

TEST1
TEST2

Each PDB has a local application schema:

APP_DEMO

Each local schema owns the same table:

APP_DEMO.SALES_ORDERS

The reporting pattern uses two common users:

User	Purpose
`C##PDB_REPORT`	Owns the common reporting views and runs the `CONTAINERS` query.
`C##PDB_READER`	Read-only user that selects from a root reporting view.

The end result is a query from CDB$ROOT that returns data from both PDBs:

CON_ID PDB_NAME ORDER_ID CUSTOMER_NAME ORDER_AMOUNT
______ ________ ________ _____________ ____________
     4 TEST1         101 Alpha Stores           120
     4 TEST1         102 Beta Retail            240
     5 TEST2         201 Gamma Corp             300
     5 TEST2         202 Delta Shop             180

Your CON_ID values may be different.

Why use CONTAINERS?

The CONTAINERS clause allows a query from the root to query same-named tables or views across containers. A simple example looks like this:

SELECT *
FROM   CONTAINERS(sales_orders_v);

That is powerful, but there are a few rules that matter for this demo:

To query data from multiple PDBs, run the query from CDB$ROOT.
The object named inside CONTAINERS(...) must exist in the root and in the target PDBs.
For local PDB application tables, a clean pattern is to create same-named views owned by a common user in each PDB.
The root object can be an empty metadata view, as long as its columns match the PDB views.

I prefer the view-based pattern because it avoids putting application data in the root. The application data stays local to each PDB, and the root only owns the reporting metadata.

Step 1: Create the common users

The demo starts in CDB$ROOT as a privileged user.

CREATE USER c##pdb_report IDENTIFIED BY "Demo#2026" CONTAINER=ALL;

GRANT CREATE SESSION,
      CREATE VIEW,
      SET CONTAINER
TO c##pdb_report CONTAINER=ALL;

CREATE USER c##pdb_reader IDENTIFIED BY "Demo#2026" CONTAINER=ALL;

GRANT CREATE SESSION
TO c##pdb_reader CONTAINER=ALL;

There is an important distinction here:

CONTAINER=ALL

makes the user and privileges common across containers. It does not automatically mean the user can see all PDB rows through a root query.

For the reporting user, the script immediately enables visibility into the two target PDBs:

ALTER USER c##pdb_report
  SET CONTAINER_DATA = (CDB$ROOT, TEST1, TEST2)
  CONTAINER=CURRENT;

But the script intentionally does not set CONTAINER_DATA for C##PDB_READER yet. That gives us a useful negative test later.

Step 2: Create local application data in each PDB

In TEST1, the demo creates a local application table and inserts a couple of rows:

ALTER SESSION SET CONTAINER = TEST1;

CREATE USER app_demo IDENTIFIED BY "Demo#2026";

GRANT CREATE SESSION,
      CREATE TABLE,
      UNLIMITED TABLESPACE
TO app_demo;

CREATE TABLE app_demo.sales_orders (
  order_id      NUMBER PRIMARY KEY,
  customer_name VARCHAR2(50),
  order_amount  NUMBER(10,2)
);

INSERT INTO app_demo.sales_orders VALUES (101, 'Alpha Stores', 120.00);
INSERT INTO app_demo.sales_orders VALUES (102, 'Beta Retail',  240.00);
COMMIT;

GRANT SELECT ON app_demo.sales_orders TO c##pdb_report WITH GRANT OPTION;

In TEST2, the same table exists, but the rows are different:

ALTER SESSION SET CONTAINER = TEST2;

CREATE USER app_demo IDENTIFIED BY "Demo#2026";

GRANT CREATE SESSION,
      CREATE TABLE,
      UNLIMITED TABLESPACE
TO app_demo;

CREATE TABLE app_demo.sales_orders (
  order_id      NUMBER PRIMARY KEY,
  customer_name VARCHAR2(50),
  order_amount  NUMBER(10,2)
);

INSERT INTO app_demo.sales_orders VALUES (201, 'Gamma Corp', 300.00);
INSERT INTO app_demo.sales_orders VALUES (202, 'Delta Shop', 180.00);
INSERT INTO app_demo.sales_orders VALUES (202, 'Sigma Shop', 550.00);

COMMIT;

GRANT SELECT ON app_demo.sales_orders TO c##pdb_report WITH GRANT OPTION;

Step 3: Create same-named views owned by the common user

Now connect as the common reporting user:

CONNECT c##pdb_report/"Demo#2026"

In CDB$ROOT, create an empty view. This view is intentionally empty. It provides the root-side object definition required by the CONTAINERS clause.

ALTER SESSION SET CONTAINER = CDB$ROOT;

CREATE OR REPLACE VIEW sales_orders_v AS
SELECT CAST(NULL AS NUMBER)       AS order_id,
       CAST(NULL AS VARCHAR2(50)) AS customer_name,
       CAST(NULL AS NUMBER(10,2)) AS order_amount
FROM   dual
WHERE  1 = 0;

Then create a same-named view in each PDB. In TEST1:

ALTER SESSION SET CONTAINER = TEST1;

CREATE OR REPLACE VIEW sales_orders_v AS
SELECT CAST(order_id AS NUMBER)              AS order_id,
       CAST(customer_name AS VARCHAR2(50))   AS customer_name,
       CAST(order_amount AS NUMBER(10,2))    AS order_amount
FROM   app_demo.sales_orders;

And in TEST2:

ALTER SESSION SET CONTAINER = TEST2;

CREATE OR REPLACE VIEW sales_orders_v AS
SELECT CAST(order_id AS NUMBER)              AS order_id,
       CAST(customer_name AS VARCHAR2(50))   AS customer_name,
       CAST(order_amount AS NUMBER(10,2))    AS order_amount
FROM   app_demo.sales_orders;

The name SALES_ORDERS_V now exists in root, TEST1, and TEST2, owned by the same common user.

Step 4: Query across PDBs from CDB$ROOT

Switch back to root and use the CONTAINERS clause:

ALTER SESSION SET CONTAINER = CDB$ROOT;

SELECT con_id,
       con_id_to_con_name(con_id) AS pdb_name,
       order_id,
       customer_name,
       order_amount
FROM   CONTAINERS(sales_orders_v)
WHERE  con_id IN (
         con_name_to_id(UPPER('TEST1')),
         con_name_to_id(UPPER('TEST2'))
       )
ORDER  BY con_id, order_id;

Example output:

CON_ID PDB_NAME ORDER_ID CUSTOMER_NAME ORDER_AMOUNT
______ ________ ________ _____________ ____________
     4 TEST1         101 Alpha Stores           120
     4 TEST1         102 Beta Retail            240
     5 TEST2         201 Gamma Corp             300
     5 TEST2         202 Delta Shop             180

The CON_ID column tells us which container returned each row. I like to include CON_ID_TO_CON_NAME(con_id) in demos because it makes the output immediately understandable.

An aggregate query also makes the cross-PDB behavior obvious:

SELECT con_id,
       con_id_to_con_name(con_id) AS pdb_name,
       COUNT(*)                   AS order_count,
       SUM(order_amount)          AS total_amount
FROM   CONTAINERS(sales_orders_v)
WHERE  con_id IN (
         con_name_to_id(UPPER('TEST1')),
         con_name_to_id(UPPER('TEST2'))
       )
GROUP  BY con_id, con_id_to_con_name(con_id)
ORDER  BY con_id;

Step 5: Hide the CONTAINERS clause behind a root reporting view

For a read-only consumer, I do not necessarily want them writing the CONTAINERS query directly. Instead, the reporting user can create a root view:

CREATE OR REPLACE VIEW sales_orders_all_v AS
SELECT con_id,
       con_id_to_con_name(con_id) AS pdb_name,
       order_id,
       customer_name,
       order_amount
FROM   CONTAINERS(sales_orders_v)
WHERE  con_id IN (
         con_name_to_id(UPPER('TEST1')),
         con_name_to_id(UPPER('TEST2'))
       );

GRANT SELECT ON sales_orders_all_v TO c##pdb_reader;

At this point, C##PDB_READER has SELECT on the root reporting view.

So does the query work?

Not yet.

The gotcha: SELECT on the view is not enough

Connect as the read-only user:

CONNECT c##pdb_reader/"Demo#2026"

Then query the root reporting view:

SELECT *
FROM   c##pdb_report.sales_orders_all_v
ORDER  BY con_id, order_id;

Expected result:

no rows selected

This is expected.

It is also the most useful teaching point in the demo.

The user has SELECT on the root view, so the SQL statement is valid. But the read-only common user does not yet have container visibility into TEST1 and TEST2. At this point, the user can only see root-level rows, and the root SALES_ORDERS_V view is intentionally empty.

That means the query succeeds, but it returns no rows.

This is not a bug. It is a security boundary.

Step 6: Enable PDB row visibility for the read-only user

Now connect back as a privileged user in CDB$ROOT and set CONTAINER_DATA for the read-only user:

CONNECT / AS SYSDBA

ALTER SESSION SET CONTAINER = CDB$ROOT;

ALTER USER c##pdb_reader
  SET CONTAINER_DATA = (CDB$ROOT, TEST1, TEST2)
  CONTAINER=CURRENT;

You can verify the setting with CDB_CONTAINER_DATA:

SELECT username,
       default_attr,
       owner,
       object_name,
       all_containers,
       container_name
FROM   cdb_container_data
WHERE  username = 'C##PDB_READER'
ORDER  BY username, default_attr DESC, owner, object_name, container_name;

Then connect as the read-only user again and run the same query:

CONNECT c##pdb_reader/"Demo#2026"

SELECT *
FROM   c##pdb_report.sales_orders_all_v
ORDER  BY con_id, order_id;

Now the rows are returned:

CON_ID PDB_NAME ORDER_ID CUSTOMER_NAME ORDER_AMOUNT
______ ________ ________ _____________ ____________
     4 TEST1         101 Alpha Stores           120
     4 TEST1         102 Beta Retail            240
     5 TEST2         201 Gamma Corp             300
     5 TEST2         202 Delta Shop             180

Same user. Same object privilege. Same query.

The difference is CONTAINER_DATA.

Why I like this pattern

This is a clean way to demonstrate cross-PDB reporting without moving application data into the root.

The pattern keeps the responsibilities clear:

Layer	Responsibility
Local PDB schema	Owns the application data.
Common reporting user	Owns same-named views and the root reporting view.
Read-only common user	Receives `SELECT` on the root view.
`CONTAINER_DATA`	Controls which PDB rows the common user can see from root.

It also gives you a nice demo moment. Before enabling CONTAINER_DATA, the reader’s query returns no rows. After enabling it, the exact same query returns data from both PDBs.

Things to watch

A few practical reminders:

The object referenced by CONTAINERS(...) needs to exist in root and the target PDBs.
Keep the root object empty when you only need metadata.
Make sure the PDB views have compatible column names and datatypes.
Use CON_ID and CON_ID_TO_CON_NAME to make the source PDB obvious.
Do not confuse CONTAINER=ALL with CONTAINER_DATA.
Be intentional about CONTAINER_DATA; listing the required PDBs is usually better for a demo than using ALL.
PDBs need to be open for the CONTAINERS query to return their rows.

Running the full demo script

The companion script is designed to be run from SQL*Plus or SQLcl while connected to CDB$ROOT as SYSDBA:

sqlplus / as sysdba @pdb_containers_clause_demo.sql

Before running it, update the customization section:

DEFINE pdb1            = TEST1
DEFINE pdb2            = TEST2
DEFINE demo_password   = Demo#2026
DEFINE report_user     = C##PDB_REPORT
DEFINE reader_user     = C##PDB_READER
DEFINE app_user        = APP_DEMO
DEFINE demo_table      = SALES_ORDERS
DEFINE demo_view       = SALES_ORDERS_V
DEFINE all_rows_view   = SALES_ORDERS_ALL_V

The script creates the users, sample data, root and PDB views, the negative test, the CONTAINER_DATA fix, and the final positive test.

Cleanup

The script prints cleanup commands at the end. The short version is:

CONNECT / AS SYSDBA
ALTER SESSION SET CONTAINER = CDB$ROOT;
DROP USER c##pdb_reader CASCADE;
DROP USER c##pdb_report CASCADE;

ALTER SESSION SET CONTAINER = TEST1;
DROP USER app_demo CASCADE;

ALTER SESSION SET CONTAINER = TEST2;
DROP USER app_demo CASCADE;

Final thought

The CONTAINERS clause is a great feature for cross-PDB demos and reporting patterns, but the key is to remember that object privileges and container visibility are separate.

GRANT SELECT answers this question:

Can the user query the object?

CONTAINER_DATA answers a different question:

Which containers’ rows can the user see from root?

For this demo, that distinction is exactly what makes the example valuable.

Demo code available here

OraMatt: YABAOracle