Nesta nova arquitetura, a mudança é transparente para quem vai consumir o banco de dados, mas em algumas tarefas de administração do ambiente (na minha opinião, principalmente de monitoração), há detalhes para considerar. Abordarei as consultas de tablespaces e datafiles neste artigo.
Logado no CDB$ROOT, consultando as tablespaces padrões permanente e temporária:
[oracle@oel8 ~]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 17 04:19:42 2021
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL> SELECT NAME,OPEN_MODE,CDB FROM V$DATABASE;
NAME OPEN_MODE CDB
--------- -------------------- ---
TALAMO READ WRITE YES
SQL> col property_name format a30
SQL> col property_value format a25
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DEFAULT_%TABLE%';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ -------------------------
DEFAULT_PERMANENT_TABLESPACE USERS
DEFAULT_TEMP_TABLESPACE TEMP
Consumindo a view CDB_TABLESPACES, podemos consultar todas as tablespaces do ambiente, neste exemplo abaixo, do CDB$ROOT e seus 2 PDBS. Vale ressaltar que nesta view não temos os dados do SEED PDB:
SQL> col pdb_name format a10
SQL> SELECT T.TABLESPACE_NAME, T.CON_ID, P. PDB_NAME FROM CDB_TABLESPACES T, CDB_PDBS P WHERE T.CON_ID = P.CON_ID (+) ORDER BY 2,1;
TABLESPACE_NAME CON_ID PDB_NAME
------------------------------ ---------- ----------
SYSAUX 1
SYSTEM 1
TEMP 1
UNDOTBS1 1
USERS 1
SYSAUX 3 HIPOFISE1
SYSTEM 3 HIPOFISE1
TEMP 3 HIPOFISE1
UNDOTBS1 3 HIPOFISE1
USERS 3 HIPOFISE1
SYSAUX 4 HIPOFISE2
TABLESPACE_NAME CON_ID PDB_NAME
------------------------------ ---------- ----------
SYSTEM 4 HIPOFISE2
TEMP 4 HIPOFISE2
UNDOTBS1 4 HIPOFISE2
USERS 4 HIPOFISE2
15 rows selected.
Seguindo a mesma lógica, a CDB_DATA_FILES retorna os datafiles do CDB$ROOT e seus 2 PDBS, exceto o SEED:
SQL> col file_name format a50
SQL> col tablespace_name format a8
SQL> col file_id format 9999
SQL> col con_id format 999
SQL> SELECT FILE_NAME, TABLESPACE_NAME, FILE_ID, CON_ID FROM CDB_DATA_FILES ORDER BY CON_ID;
FILE_NAME TABLESPA FILE_ID CON_ID
-------------------------------------------------- -------- ------- ------
/oracle/dados/TALAMO/datafile/o1_mf_system_j3w17w2 SYSTEM 1 1
6_.dbf
/oracle/dados/TALAMO/datafile/o1_mf_undotbs1_j3w1c UNDOTBS1 4 1
yoh_.dbf
/oracle/dados/TALAMO/datafile/o1_mf_users_j3w1czy4 USERS 7 1
_.dbf
/oracle/dados/TALAMO/datafile/o1_mf_sysaux_j3w1bkd SYSAUX 3 1
m_.dbf
FILE_NAME TABLESPA FILE_ID CON_ID
-------------------------------------------------- -------- ------- ------
/oracle/dados/TALAMO/BCF7B920CA5C126AE0536B00A8C0B SYSTEM 9 3
4F1/datafile/o1_mf_system_j4b7tm5g_.dbf
/oracle/dados/TALAMO/BCF7B920CA5C126AE0536B00A8C0B USERS 12 3
4F1/datafile/o1_mf_users_j4b81g2r_.dbf
/oracle/dados/TALAMO/BCF7B920CA5C126AE0536B00A8C0B UNDOTBS1 11 3
4F1/datafile/o1_mf_undotbs1_j4b7tm63_.dbf
/oracle/dados/TALAMO/BCF7B920CA5C126AE0536B00A8C0B SYSAUX 10 3
FILE_NAME TABLESPA FILE_ID CON_ID
-------------------------------------------------- -------- ------- ------
4F1/datafile/o1_mf_sysaux_j4b7tm62_.dbf
/oracle/dados/TALAMO/BD02D62DBD0C163FE0536B00A8C0E SYSTEM 13 4
F43/datafile/o1_mf_system_j4cpg63g_.dbf
/oracle/dados/TALAMO/BD02D62DBD0C163FE0536B00A8C0E USERS 16 4
F43/datafile/o1_mf_users_j4cpgr31_.dbf
/oracle/dados/TALAMO/BD02D62DBD0C163FE0536B00A8C0E UNDOTBS1 15 4
F43/datafile/o1_mf_undotbs1_j4cpg640_.dbf
FILE_NAME TABLESPA FILE_ID CON_ID
-------------------------------------------------- -------- ------- ------
/oracle/dados/TALAMO/BD02D62DBD0C163FE0536B00A8C0E SYSAUX 14 4
F43/datafile/o1_mf_sysaux_j4cpg640_.dbf
12 rows selected.
Mas caso usemos a DBA_DATA_FILES, apenas os datafiles do CDB$ROOT (que é o current container) serão retornados:
SQL> SELECT FILE_NAME, TABLESPACE_NAME, FILE_ID FROM DBA_DATA_FILES;
FILE_NAME TABLESPA FILE_ID
-------------------------------------------------- -------- -------
/oracle/dados/TALAMO/datafile/o1_mf_system_j3w17w2 SYSTEM 1
6_.dbf
/oracle/dados/TALAMO/datafile/o1_mf_sysaux_j3w1bkd SYSAUX 3
m_.dbf
/oracle/dados/TALAMO/datafile/o1_mf_users_j3w1czy4 USERS 7
_.dbf
/oracle/dados/TALAMO/datafile/o1_mf_undotbs1_j3w1c UNDOTBS1 4
yoh_.dbf
FILE_NAME TABLESPA FILE_ID
-------------------------------------------------- -------- -------
Outra alternativa, é usarmos as views V$TABLESPACE/V$DATAFILE, que retornam as informações do CDB$ROOT e seus PDBS, INCLUINDO o SEED PDB:
SQL> col name format a12
SQL> SELECT FILE#, T.NAME, T.TS#, T.CON_ID FROM V$DATAFILE D, V$TABLESPACE T WHERE D.TS#=T.TS# AND D.CON_ID=T.CON_ID ORDER BY 4,3;
FILE# NAME TS# CON_ID
---------- ------------ ---------- ------
1 SYSTEM 0 1
3 SYSAUX 1 1
4 UNDOTBS1 2 1
7 USERS 4 1
5 SYSTEM 0 2
6 SYSAUX 1 2
8 UNDOTBS1 2 2
9 SYSTEM 0 3
10 SYSAUX 1 3
11 UNDOTBS1 2 3
12 USERS 5 3
FILE# NAME TS# CON_ID
---------- ------------ ---------- ------
13 SYSTEM 0 4
14 SYSAUX 1 4
15 UNDOTBS1 2 4
16 USERS 5 4
15 rows selected.
Alterando o container para o SEED e consultando seus datafiles, é possível perceber que o OMF joga os arquivos dentro do mesmo diretório do CDB$ROOT:
SQL> ALTER SESSION SET CONTAINER=PDB$SEED;
Session altered.
SQL> col file_name format a80
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
FILE_NAME
--------------------------------------------------------------------------------
/oracle/dados/TALAMO/datafile/o1_mf_system_j3w1q776_.dbf
/oracle/dados/TALAMO/datafile/o1_mf_sysaux_j3w1q76z_.dbf
/oracle/dados/TALAMO/datafile/o1_mf_undotbs1_j3w1q779_.dbf
Outro comportamento a ser observado: quando logamos em um PDB e usamos uma view do tipo CDB, apenas as informações daquele PDB é retornada:
SQL> ALTER SESSION SET CONTAINER=HIPOFISE1;
Session altered.
SQL> COL FILE_NAME FOR A50
SQL> SELECT FILE_NAME,CON_ID FROM CDB_DATA_FILES;
FILE_NAME CON_ID
-------------------------------------------------- ------
/oracle/dados/TALAMO/BCF7B920CA5C126AE0536B00A8C0B 3
4F1/datafile/o1_mf_system_j4b7tm5g_.dbf
/oracle/dados/TALAMO/BCF7B920CA5C126AE0536B00A8C0B 3
4F1/datafile/o1_mf_sysaux_j4b7tm62_.dbf
/oracle/dados/TALAMO/BCF7B920CA5C126AE0536B00A8C0B 3
4F1/datafile/o1_mf_undotbs1_j4b7tm63_.dbf
/oracle/dados/TALAMO/BCF7B920CA5C126AE0536B00A8C0B 3
4F1/datafile/o1_mf_users_j4b81g2r_.dbf
FILE_NAME CON_ID
-------------------------------------------------- ------
O mesmo conceito acontece com as views V$:
SQL> SHOW CON_NAME CON_ID
CON_NAME
------------------------------
HIPOFISE1
CON_ID
------------------------------
3
SQL> COL NAME FOR A50
SQL> SELECT NAME,CON_ID FROM V$DATAFILE;
NAME CON_ID
-------------------------------------------------- ------
/oracle/dados/TALAMO/BCF7B920CA5C126AE0536B00A8C0B 3
4F1/datafile/o1_mf_system_j4b7tm5g_.dbf
/oracle/dados/TALAMO/BCF7B920CA5C126AE0536B00A8C0B 3
4F1/datafile/o1_mf_sysaux_j4b7tm62_.dbf
/oracle/dados/TALAMO/BCF7B920CA5C126AE0536B00A8C0B 3
4F1/datafile/o1_mf_undotbs1_j4b7tm63_.dbf
/oracle/dados/TALAMO/BCF7B920CA5C126AE0536B00A8C0B 3
4F1/datafile/o1_mf_users_j4b81g2r_.dbf
NAME CON_ID
-------------------------------------------------- ------
Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.
Portuguese
English
Spanish