SQL & PL/SQL

DB version : 19c

Bit of a newbie question from a DBA.

In a CDB, I have several PDBs (pluggable databases) with the user SCOTTY.
SCOTTY user in pluggable database P_DB4_U has a table named EMP_MASTER.

Although PDB_SALES and PDB_HRMS have SCOTTY user, the SCOTTY user does not have EMP_MASTER.

From the root container, I want to run a query which will return all PDBs which has the SCOTTY user but not having EMP_MASTER table.
How can I do that ?

Query1 shown at the bottom correctly lists PDBs which have a SCOTTY user and EMP_MASTER table.

I was hoping Query 2 will return all PDBs which has the SCOTTY user but not having EMP_MASTER table. But, it is not giving correct results. I tried Query3 with NOT EXISTS . But, Query 3 is giving wrong results.

Any workaround ?

SQL> show pdbs

   CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
        2 PDB$SEED                       READ ONLY  NO
        3 PDB_FNP                        READ ONLY  NO
        4 PDB_STR                        READ ONLY  NO
        5 PDB_WMOS                       MOUNTED
        6 PDB_PYRL                       READ ONLY  NO
        7 DBFS                           READ WRITE NO
        8 PDB_SALES                      READ WRITE NO
       10 P_DB4_U                        READ WRITE NO
       11 PDB_HRMS                       READ WRITE NO
SQL>
SQL>
SQL> alter session set container = PDB_SALES;
Session altered.

SQL> create user scotty identified by nH#8Lman;
User created.

SQL> alter session set container = PDB_HRMS;
Session altered.

SQL> create user scotty identified by Test#y83;
User created.

SQL>
SQL> alter session set container = P_DB4_U;
Session altered.

SQL> create user scotty identified by Cedar#763 default tablespace users;
User created.

SQL> alter user scotty quota 5m on users;
User altered.

SQL> grant create table to scotty;
Grant succeeded.

SQL> grant CREATE SESSION to scotty;
Grant succeeded.

SQL> conn scotty/Cedar#763@P_DB4_U
Connected.

SQL> create table emp_master (empname varchar2(25));
Table created.

--- Conncting to root container

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

Query1.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

col name for a10

SELECT     vp.name,
          cu.con_id
FROM       v$pdbs vp
inner join cdb_users cu on  (vp.con_id = cu.con_id)
inner join cdb_tables ct on  (ct.con_id = cu.con_id)
where      cu.username LIKE 'SCOTT%'
AND        ct.table_name = 'EMP_MASTER';

NAME           CON_ID
---------- ----------
P_DB4_E            10


--- Query2 (flawed query)
SELECT    distinct vp.name,
          cu.con_id
FROM       v$pdbs vp
inner join cdb_users cu on  (vp.con_id = cu.con_id)
inner join cdb_tables ct on  (ct.con_id = cu.con_id)
AND        (vp.con_id = ct.con_id)
where      cu.username LIKE 'SCOTT%'
AND        ct.table_name != 'EMP_MASTER';

NAME           CON_ID
---------- ----------
PDB_HRMS           11
PDB_SALES           8
P_DB4_U            10


-- Query3 (another flawed query)

SELECT    vp.name,
           cu.con_id
FROM       v$pdbs vp
inner join cdb_users cu on  (vp.con_id = cu.con_id)
inner join cdb_tables ct on  (ct.con_id = cu.con_id)
AND        (vp.con_id = ct.con_id)
where      cu.username LIKE 'SCOTT%'
AND  not exists ( select 1 from cdb_tables where ct.table_name = 'EMP_MASTER');