Zuerst werden in der CDB, der PDB$SEED und den weiteren PDBs je ein Tablespace „TEST“ mit unterschiedlicher Größe erstellt, um die Unterschiede bei den unterschiedlichen Abfragen zu verdeutlichen.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBEINS READ WRITE NO
4 PDBZWEI READ WRITE NO
SQL> -- Anlage von TEST Tablespaces in den PDBs und der CDB
SQL> alter session set container=PDBEINS;
SQL> create tablespace TEST datafile size 10M;
SQL> alter session set container=PDBZWEI;
SQL> create tablespace TEST datafile size 20M;
SQL> alter session set container=CDB$ROOT;
SQL> create tablespace TEST datafile size 1M;
SQL> -- Die PDB$SEED wird schreibend geoeffnet und auch dort eine TEST Tablespace erstellt
SQL> alter pluggable database PDB$SEED close instances=ALL;
SQL> alter pluggable database PDB$SEED open instances=ALL;
SQL> alter session set container=PDB$SEED;
SQL> create tablespace TEST datafile size 2M;
SQL> -- Abschliessend oeffnen der PDB$SEED im READ ONLY Mode
SQL> alter session set container=CDB$ROOT;
SQL> alter pluggable database PDB$SEED close instances=ALL;
SQL> alter pluggable database PDB$SEED open read only instances=ALL;
Mit Einführung der Multitenant Umgebung hat Oracle dem Administrator neben den ALL_*, USER_* und den DBA_* neu die CDB_* Views spendiert. Die CDB_* Views sind ein Abbild der DBA_* Views, erweitert um eine Spalte CON_ID, die die ID des Containers ausgibt.
In einer herkömmlichen (non-CDB) Datenbank existieren diese Views ebenfalls, obwohl es keine Container gibt. Dies erkennt man an der CON_ID=0.
Die CDB (Containername CDB$ROOT) hat immer die CON_ID=1.
Der Container PDB$SEED bekommt immer die CON_ID=2.
Alle anderen selbst erstellten PDBs erhalten eine fortlaufende ID > 2.
In den meisten Fällen werden in den CDB_* Views keine Informationen zur PDB$SEED (CON_ID=2) ausgegeben.
Ist man auf einer PDB angemeldet so werden nur Daten zu der PDB ausgegeben, auf der CDB jedoch werden die Daten zu der CDB und aller PDBs präsentiert:
SQL> alter session set container=PDBEINS
SQL> -- Abfrage auf DBA_* View in einer PD
SQL> select TABLESPACE_NAME, bytes/1024/1024 MB
from dba_data_files where TABLESPACE_NAME ='TEST'
TABLESPACE_NAME MB
------------------------------ ----------
TEST 1
SQL> -- Abfrage auf CDB_* View in einer PDB (Anzeige nur des TBS der akt. PDB
SQL> select CON_ID, TABLESPACE_NAME, bytes/1024/1024 MB
from cdb_data_files where TABLESPACE_NAME ='TEST' order by con_id
CON_ID TABLESPACE_NAME MB
---------- ------------------------------ ----------
3 TEST 1
SQL> -- Abfrage auf DBA_* View in der CD
SQL> alter session set container=CDB$ROOT;
SQL> select TABLESPACE_NAME, bytes/1024/1024 MB
from dba_data_files where TABLESPACE_NAME ='TEST'
TABLESPACE_NAME MB
------------------------------ ----------
TEST
SQL> -- Wechsel in die CDB
SQL> alter session set container=CDB$ROOT;
SQL> -- Abfrage auf CDB_* View in der CDB (Anzeige der TBS der CDB und der PDBs
SQL> select CON_ID, TABLESPACE_NAME, bytes/1024/1024 MB
from cdb_data_files where TABLESPACE_NAME ='TEST' order by con_id
CON_ID TABLESPACE_NAME MB
---------- ------------------------------ ----------
1 TEST 1
3 TEST 10
4 TEST 20
Auch alle V$ Views haben eine Spalte CON_ID erhalten, mit der der entsprechende Container ermittelt werden kann.
Achtung: Bei Views die nicht CDB/PDB spezifische Informationen anzeigen, deren Informationen generell gelten (z. B. bei v$log oder v$logfile) wird die CON_ID = 0 verwendet.
Es werden teilweise auch Informationen zur PDB$SEED (CON_ID=2) ausgegeben.
SQL> alter session set container=PDBZWEI;
SQL> -- Abfrage auf v$* View in einer PDB (Anzeige nur des TBS der akt. PDB)
SQL> select f.con_id, FILE#, t.TS#, t.name, f.bytes/1024/1024 MB
from v$datafile f, v$tablespace t
where f.TS# = t.TS# and f.con_id= t.con_id and t.name = 'TEST';
CON_ID FILE# TS# NAME MB
---------- ---------- ---------- -------------------- ----------
4 30 6 TEST 20
SQL> -- Abfrage auf v$log (Anzeige der Online Redo Logs mit CON_ID=0)
SQL> select CON_ID,GROUP#, THREAD# from v$log;
CON_ID GROUP# THREAD#
---------- ---------- ----------
0 1 1
0 2 1
0 3 2
0 4 2
SQL> alter session set container=CDB$ROOT;
SQL> -- Abfrage auf v$* View in einer CDB (Anzeige der TBS in der CDB und aller PDBs.
SQL> -- incl. der PDB$SEED)
SQL> select f.con_id, FILE#, t.TS#, t.name, f.bytes/1024/1024 MB
from v$datafile f, v$tablespace t
where f.TS# = t.TS# and f.con_id= t.con_id and t.name = 'TEST' order by CON_ID;
CON_ID FILE# TS# NAME MB
---------- ---------- ---------- -------------------- ----------
1 31 6 TEST 1
2 33 5 TEST 2
3 29 6 TEST 10
4 30 6 TEST 20
SQL> -- Abfrage auf v$log (Anzeige der Online Redo Logs mit CON_ID=0)
SQL> select CON_ID,GROUP#, THREAD# from v$log;
CON_ID GROUP# THREAD#
---------- ---------- ----------
0 1 1
0 2 1
0 3 2
0 4 2
Bei Abfragen auf *$ Tabellen ist Vorsicht geboten, diese haben keine CON_ID, und zeigen in der Regel nur die Inhalte des aktuellen Kontextes an.
SQL> alter session set container=PDBZWEI;
SQL> -- In der PDB wird nur der TBS der jeweiligen PDB angezeigt
SQL> select FILE#, TS# from file$ where FILE# >=29;
FILE# TS#
---------- ----------
30 6
SQL> alter session set container=CDB$ROOT;
SQL> -- In der CDB wird nur der TBS der CDB angezeigt
SQL> select FILE#, TS# from file$ where FILE# >=29;
FILE# TS#
---------- ----------
31 6
Wenn man sich mit der CDB verbinden um auch Informationen über die PDBs abzufragen, muss man nicht nur darauf achten welche Typen (CDB_* oder V$*) man in den Select Statements verwendet, sondern auch ob die PDB geöffnet ist. Dies ist vor allem im RAC Umfeld wichtig, da hier die PDB ggf. nicht in allen Instanzen geöffnet ist.
Generell gilt, Selects auf V$* Views liefern zum Teil Ergebnisse, auch wenn die PDB geschlossen ist.
Selects auf CDB_* Views liefern in der Regel kein Ergebnis wenn die PDB komplett geschlossen ist.
Im RAC Umfeld kann es sein, dass z. B. bei Wartungsarbeiten auf einer CDB Instanz eine PDB geschlossen ist, aber auf minimum einer anderen CDB Instanz die PDB läuft. Hier kann der Session Parameter parallel_force_local helfen ein Ergebnis anzuzeigen. Ist dieser auf „FALSE“ gesetzt so kann die CDB die angeforderten Informationen von einer anderen Instanz auf der die PDB läuft zur Verfügung stellen. Ist eine PDB auf allen Instanzen gestoppt so können in der Regel bei den CDB_* Views die Informationen zur gestoppten PDB nicht abgefragt werden (Ausnahme z. B. CDB_PDBS).
parallel_force_local = TRUE führt in RAC-Systemen parallele Abfragen immer nur auf der lokalen Instanz aus. Bei FALSE werden parallel Queries auf allen RAC Knoten abgesetzt und somit können die CDB_* Views dort die benötigten Informationen sammeln.
Achtung: Ein Umsetzen des Parameters kann ggf. negative Auswirkungen auf die Applikationen bewirken, daher den Parameter nur innerhalb der Session setzen und ggf. nach dem entspr. SELECT wieder zurücknehmen.
SQL> alter session set container=CDB$ROOT;
SQL> select INST_ID,CON_ID,NAME,OPEN_MODE from gv$pdbs order by CON_ID,INST_ID;
INST_ID CON_ID NAME OPEN_MODE
---------- ---------- -------------------- ----------
1 2 PDB$SEED READ ONLY
2 2 PDB$SEED READ ONLY
1 3 PDBEINS READ WRITE
2 3 PDBEINS READ WRITE
1 4 PDBZWEI READ WRITE
2 4 PDBZWEI READ WRITE
SQL> show parameter parallel_force_local
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_force_local boolean TRUE
SQL> select CON_ID, TABLESPACE_NAME, bytes/1024/1024 MB from cdb_data_files
where TABLESPACE_NAME ='TEST' order by con_id;
CON_ID TABLESPACE_NAME MB
---------- ------------------------------ ----------
1 TEST 1
3 TEST 10
4 TEST 20
SQL> select f.con_id,t.name, f.bytes/1024/1024 MB from v$datafile f, v$tablespace t
where f.TS# = t.TS# and f.con_id= t.con_id and t.name = 'TEST' order by CON_ID;
CON_ID NAME MB
---------- -------------------- ----------
1 TEST 1
2 TEST 2
3 TEST 10
4 TEST 20
SQL> -- Stoppen der PDBEINS nur auf der ersten/aktuellen Instanz
SQL> alter pluggable database PDBEINS close instances=('CDBA1');
Pluggable database altered.
SQL> select INST_ID,CON_ID,NAME,OPEN_MODE from gv$pdbs order by CON_ID,INST_ID;
INST_ID CON_ID NAME OPEN_MODE
---------- ---------- -------------------- ----------
1 2 PDB$SEED READ ONLY
2 2 PDB$SEED READ ONLY
1 3 PDBEINS MOUNTED
2 3 PDBEINS READ WRITE
1 4 PDBZWEI READ WRITE
2 4 PDBZWEI READ WRITE
SQL> -- der Tabelspace wird nicht mehr angezeigt, da die PDB auf der akt. Instanz gestoppt ist
SQL> select CON_ID, TABLESPACE_NAME, bytes/1024/1024 MB from cdb_data_files
where TABLESPACE_NAME ='TEST' order by con_id;
CON_ID TABLESPACE_NAME MB
---------- ------------------------------ ----------
1 TEST 1
4 TEST 20
SQL> -- Die V$* views zeigen allerdings noch die Informationen zur geschlossenen PDB an
SQL> select f.con_id,t.name, f.bytes/1024/1024 MB from v$datafile f, v$tablespace t
where f.TS# = t.TS# and f.con_id= t.con_id and t.name = 'TEST' order by CON_ID;
CON_ID NAME MB
---------- -------------------- ----------
1 TEST 1
2 TEST 2
3 TEST 10
4 TEST 20
SQL> -- setzen des Session Parameters
SQL> alter session set parallel_force_local=false;
SQL> -- der Tabelspace auf der aktuellen Instanz gestoppten PDB wird mit angezeigt!
SQL> select CON_ID, TABLESPACE_NAME, bytes/1024/1024 MB from cdb_data_files
where TABLESPACE_NAME ='TEST' order by con_id;
CON_ID TABLESPACE_NAME MB
---------- ------------------------------ ----------
1 TEST 1
3 TEST 10
4 TEST 20
SQL> select f.con_id,t.name, f.bytes/1024/1024 MB from v$datafile f, v$tablespace t
where f.TS# = t.TS# and f.con_id= t.con_id and t.name = 'TEST' order by CON_ID;
CON_ID NAME MB
---------- -------------------- ----------
1 TEST 1
2 TEST 2
3 TEST 10
4 TEST 20
SQL> alter session set parallel_force_local=true;
SQL> select CON_ID, TABLESPACE_NAME, bytes/1024/1024 MB from cdb_data_files
where TABLESPACE_NAME ='TEST' order by con_id;
CON_ID TABLESPACE_NAME MB
---------- ------------------------------ ----------
1 TEST 1
4 TEST 20
SQL> -- Wird die PDB komplett geschlossen, so zeigen die CDB_*-Views die PDB nicht
SQL> -- mehr an, egal wie der Parameter 'parallel_force_local' gesetzt ist
SQL> alter pluggable database PDBEINS close instances=ALL;
SQL> alter session set parallel_force_local=false;
SQL> select CON_ID, TABLESPACE_NAME, bytes/1024/1024 MB from cdb_data_files
where TABLESPACE_NAME ='TEST' order by con_id;
CON_ID TABLESPACE_NAME MB
---------- ------------------------------ ----------
1 TEST 1
4 TEST 20
SQL> select f.con_id,t.name, f.bytes/1024/1024 MB from v$datafile f, v$tablespace t
where f.TS# = t.TS# and f.con_id= t.con_id and t.name = 'TEST' order by CON_ID;
CON_ID NAME MB
---------- -------------------- ----------
1 TEST 1
2 TEST 2
3 TEST 10
4 TEST 20
Das folgende Beispielskript kann als Vorlage für das Monitoring oder für DBA-Abfragen verwendet werden. Im ersten Abschnitt wird geprüft, ob man an einer non-CDB, CDB oder PDB angemeldet ist, incl. Exceptionhandling, um das Skript zu beenden wenn die Oracle Version < 12 ist (dort gibt es die CDB-Views sowie die Spalten CON_ID noch nicht).
Nur wenn man an einer CDB angemeldet ist, wird über Bind Variable v_cdb bzw. die substitution Variable IF_CDB, die im ersten Abschnitt ermittelt wird, gesteuert ob die Spalte „Container“ angezeigt wird oder nicht (NOPRINT). Die Anzeige der Spalte con_id wird im Skript mit NOPRINT unterdrückt.
Zudem wird der aktuelle Wert des Session Parameter parallel_force_local in der substitution Variable col_old_param zwischengespeichert.
Nur wenn der Session Parameter parallel_force_local auf dem Wert true steht wird dieser auf false geändert.
Anschließend werden zwei Beispiel Abfragen ausgegeben.
Beim ersten Select wird auf einer CDB oder PDB angemeldet als erste Spalte der Container-Name ausgeben. Auf non-CDB Datenbanken wird die Spalte Container unterdrückt.
Beim zweiten Select wird auf einer CDB oder PDB angemeldet die Spalte „Tablespace“ um die Information des Containers ergänzt.
Zum Abschluss wird der Session Parameter parallel_force_local wieder auf den ursprünglichen Wert und alle Colums auf printable gesetzt, damit nachfolgende Abfragen nicht beeinflusst werden.
Beispiel Skript:
cat cdb_test.sq
set termout on
set pagesize 60
set linesize 300
SET verify off
set feedback off
set heading off
set serveroutput o
-- +----------------------------------------------------------------------------------------+
-- | check if DB is CDB/PDB
-- +----------------------------------------------------------------------------------------+
-- define bind varable
variable v_cdb varchar2(8);
-- get value for bind variable incl. exception handling for version below Oracle 12
declare
v_statement varchar2(2000);
no_con_id_error EXCEPTION;
no_usernv_error EXCEPTION;
PRAGMA exception_init ( no_usernv_error, -02003 );
PRAGMA exception_init ( no_con_id_error, -00904 );
begin
v_statement:='select decode((decode(CDB,''YES'',1,0) * sys_context (''USERENV'',''CON_ID'')), 1, '''',''NOPRINT'' ) from V$DATABASE';
execute immediate v_statement into :v_cdb;
exception
when no_con_id_error or no_usernv_error then
:v_cdb := 'NOPRINT';
when others then
raise;
end;
/
-- define column col_CDB with new substitution variable IF_CDB
column col_CDB new_value IF_CDB noprint
-- pass the bind variable into new substitution variable IF_CDB using a query
select :v_cdb col_CDB from dual
-- save actual value for parallel_force_local
column col_old_param new_value col_old_param noprint
select VALUE col_old_param from v$parameter where lower(name) = 'parallel_force_local'
prompt actual value for parallel_force_local=&col_old_param
set feedback of
-- set parallel_force_local=FALSE only if it is TRUE
declare
v_alter_session varchar2(200);
begin
begin
select 'alter session set parallel_force_local=FALSE' into v_alter_session from v$parameter where lower(name) = 'parallel_force_local' and upper('&col_old_param') = 'TRUE';
exception
when no_data_found then
v_alter_session := '';
end;
if v_alter_session is not null then
dbms_output.put_line(v_alter_session);
execute immediate v_alter_session;
else
dbms_output.put_line('parallel_force_local is already FALSE');
end if;
end;
-- show actual value for session parameter 'parallel_force_local'
select name||'='||VALUE test from v$parameter where lower(name) = 'parallel_force_local'
col con_id noprint
-- do not display the column "container", if you run the script in a non-CDB
col container format a20 heading 'Container' justify c word_wrapped &IF_CDB
col TABLESPACE_NAME format a40 heading 'Tablespace' justify c word_wrapped
col MB format 999999999 heading 'MB'
set heading o
-- only for CDB or PDB, display the container name as first column
SELECT con_id, ( select c.NAME from v$containers c where c.con_id=a.con_id ) container,
TABLESPACE_NAME, bytes/1024/1024 MB from cdb_data_files a
where TABLESPACE_NAME ='TEST' order by con_id
-- only for CDB or PDB, display the container name and the tablespace name,
-- otherwise only the tablespace name
SELECT con_id,
( select c.NAME || ':' from v$containers c where c.con_id=a.con_id and c.con_id>0) ||
TABLESPACE_NAME TABLESPACE_NAME, bytes/1024/1024 MB from cdb_data_files a
where TABLESPACE_NAME ='TEST' order by con_id
set heading off
-- set parallel_force_local to saved value, only if it is different to saved value
declare
v_alter_session varchar2(200);
begin
begin
select 'alter session set parallel_force_local=&col_old_param' into v_alter_session from v$parameter where lower(name) = 'parallel_force_local' and upper(VALUE) != upper('&col_old_param');
exception
when no_data_found then
v_alter_session := '';
end;
if v_alter_session is not null the
dbms_output.put_line(v_alter_session);
execute immediate v_alter_session;
else
dbms_output.put_line('parallel_force_local is already FALSE');
end if;
end;
-- show actual value for session parameter 'parallel_force_local'
select name||'='||VALUE test from v$parameter where lower(name) = 'parallel_force_local'
-- set all columns to "printable" otherwise they won’t be visible
-- in further selects, reset parameter
col con_id print
col col_CDB print
col container print
col col_old_param prin
set heading on
Ausgabe des Beispielskriptes ausgeführt auf einer CDB mit parallel_force_local=TRUE:
SQL> @cdb_test.sql
actual value for parallel_force_local=TRUE
alter session set parallel_force_local=FALSE
parallel_force_local=FALSE
Container Tablespace MB
-------------------- ---------------------------------------- ----------
CDB$ROOT TEST 1
PDBZWEI TEST 20
Tablespace MB
---------------------------------------- ----------
CDB$ROOT:TEST 1
PDBZWEI:TEST 20
alter session set parallel_force_local=TRUE
parallel_force_local=TRUE
Ausgabe des Beispielskriptes ausgeführt auf einer CDB mit parallel_force_local=FALSE:
SQL> @cdb_test.sql
actual value for parallel_force_local=FALSE
parallel_force_local is already FALSE
parallel_force_local=FALSE
Container Tablespace MB
-------------------- ---------------------------------------- ----------
CDB$ROOT TEST 1
PDBZWEI TEST 20
Tablespace MB
---------------------------------------- ----------
CDB$ROOT:TEST 1
PDBZWEI:TEST 20
parallel_force_local is already FALSE
parallel_force_local=FALSE
Ausgabe des Beispielskriptes ausgeführt auf einer non-CDB:
SQL> @cdb_test.sql
actual value for parallel_force_local=TRUE
alter session set parallel_force_local=FALSE
parallel_force_local=FALSE
Tablespace MB
---------------------------------------- ----------
TEST 40
Tablespace MB
---------------------------------------- ----------
TEST 40
alter session set parallel_force_local=TRUE
Wird bei den Abfragen darauf geachtet welche Art von Views verwendet werden, und ggf. der Session Parameter parallel_force_local umgesetzt so können auf der CDB auch die dort laufenden PDBs mit überwacht werden.
Ältere Statements die noch auf *$ Tabellen basieren sollten nicht mehr verwendet werden!
Das oben aufgeführte Beispiel Skript lässt sich sowohl auf einer non-CDB, CDB oder PDB-Datenbank ausführen.
Das erste der SELECT-Statements ist eher für Skripte, die der DBA ausführt geeignet, da hier ggf. die Container Spalte ein- oder ausgeblendet wird.
Das zweite SELECT-Statement findet eher bei Überwachungen den Einsatz, da hier die Spaltenanzahl gleich bleibt und ggf. nur dem Tablespacenamen der Container vorangestellt wird.
to be continued
In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den
Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.
