In a multitenant configuration, if you are querying X$ structures and fixed tables (OBJ$, TAB$ etc.), you will face differents behaviour depending on the fact you are connected to the root container or to a pluggable database.
- If you are connected to a root container (CDB$ROOT)
- In fixed tables, for example OBJ$, there’s no CON_ID column defined in this table so this will give you all objects for your root container, and not for all containers.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> desc obj$
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJ# NOT NULL NUMBER
DATAOBJ# NUMBER
OWNER# NOT NULL NUMBER
NAME NOT NULL VARCHAR2(128)
NAMESPACE NOT NULL NUMBER
SUBNAME VARCHAR2(128)
TYPE# NOT NULL NUMBER
CTIME NOT NULL DATE
MTIME NOT NULL DATE
STIME NOT NULL DATE
STATUS NOT NULL NUMBER
REMOTEOWNER VARCHAR2(128)
LINKNAME VARCHAR2(128)
FLAGS NUMBER
OID$ RAW(16)
SPARE1 NUMBER
SPARE2 NUMBER
SPARE3 NUMBER
SPARE4 VARCHAR2(1000)
SPARE5 VARCHAR2(1000)
SPARE6 DATE
SIGNATURE RAW(16)
SPARE7 NUMBER
SPARE8 NUMBER
SPARE9 NUMBER
- X$ structures will give you information for all containers (ROOT$CDB, SEED and and all pluggable database). And there’s a CON_ID column defined in all those structures.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> desc x$ksppi
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
CON_ID NUMBER
KSPPINM VARCHAR2(80)
KSPPITY NUMBER
KSPPDESC VARCHAR2(255)
KSPPIFLG NUMBER
KSPPILRMFLG NUMBER
KSPPIHASH NUMBER
SQL> select c.name,KSPPINM
2 from x$ksppi x, v$containers c
3 where x.con_id=c.con_id and KSPPINM='open_cursors';
NAME KSPPINM
------------------------------ ------------------------------
CDB$ROOT open_cursors
PDB$SEED open_cursors
PDB1 open_cursors
PDB2 open_cursors
- If you are connected to a PDB
- In fixed tables, for example OBJ$, there’s still no CON_ID, so this will give you all objects on your PDB.
- In X$ structures, there’s a CON_ID column, but if you are connected to a PDB, you will see only the data related to this PDB.
To summarize, static fixed tables contain data for the actual container (CDB$ROOT or PDB), but X$ structures contain data for all containers if you are connected to the CDB$ROOT container, and contain data related to the PDB you are connected to, in case of a PDB.
If you disassemble static views like CDB_TABLES, you will see a new function used to have data for all containers. This is CDB$VIEW function :
SQL> show con_id
CON_ID
------------------------------
1
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> select count(*) from obj$;
COUNT(*)
----------
91005
SQL> select con_id,count(*) from CDB$VIEW("SYS"."OBJ$") group by con_id order by 1;
CON_ID COUNT(*)
---------- ----------
1 91005
2 90708
3 90960
4 90948
Ok, now we have the information for all our containers. But if we want to join an X$ Structure and a CDB$VIEW transformed object, time for execute this is too long:
select x.con_id,o.name,count(*)
from x$bh x, (select name,dataobj#,con_id from CDB$VIEW("SYS"."OBJ$")) o
where x.con_id=o.con_id
and o.dataobj#=x.obj
and o.name like 'T_PDB%'
group by x.con_id,o.name
/
... never ends :(
To execute it in a better time, I used query factorization with a WITH block, and forced materialization of it:
SQL> with o as (select /*+ MATERIALIZE */ name,dataobj#,con_id from CDB$VIEW("SYS"."OBJ$"))
2 select x.con_id,o.name,count(*)
3 from x$bh x,o
4 where x.con_id=o.con_id
5 and o.dataobj#=x.obj
6 and o.name like 'T_PDB%'
7 group by x.con_id,o.name
8 /
CON_ID NAME COUNT(*)
---------- -------------------- ----------
4 T_PDB2 9
3 T_PDB1 9
Elapsed: 00:00:02.40
Like this:
Like Loading...
Related
Data ... as usual 