Nesta nova arquitetura, a mudan\u00e7a \u00e9 transparente para quem vai consumir o banco de dados, mas em algumas tarefas de administra\u00e7\u00e3o do ambiente (na minha opini\u00e3o, principalmente de monitora\u00e7\u00e3o), h\u00e1 detalhes para considerar. Abordarei as consultas de tablespaces e datafiles neste artigo.<\/p>\n\n\n\n
Logado no CDB$ROOT, consultando as tablespaces padr\u00f5es permanente e tempor\u00e1ria:<\/p>\n\n\n
\n[oracle@oel8 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 17 04:19:42 2021\nVersion 18.3.0.0.0\n \nCopyright (c) 1982, 2018, Oracle. All rights reserved.\n \n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.3.0.0.0\n \nSQL> SELECT NAME,OPEN_MODE,CDB FROM V$DATABASE;\n \nNAME OPEN_MODE CDB\n--------- -------------------- ---\nTALAMO READ WRITE YES\n \nSQL> col property_name format a30\nSQL> col property_value format a25\nSQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DEFAULT_%TABLE%';\n \nPROPERTY_NAME PROPERTY_VALUE\n------------------------------ -------------------------\nDEFAULT_PERMANENT_TABLESPACE USERS\nDEFAULT_TEMP_TABLESPACE TEMP\n<\/pre><\/div>\n\n\nConsumindo 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\u00e3o temos os dados do SEED PDB:<\/p>\n\n\n
\nSQL> col pdb_name format a10\nSQL> 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;\n \nTABLESPACE_NAME CON_ID PDB_NAME\n------------------------------ ---------- ----------\nSYSAUX 1\nSYSTEM 1\nTEMP 1\nUNDOTBS1 1\nUSERS 1\nSYSAUX 3 HIPOFISE1\nSYSTEM 3 HIPOFISE1\nTEMP 3 HIPOFISE1\nUNDOTBS1 3 HIPOFISE1\nUSERS 3 HIPOFISE1\nSYSAUX 4 HIPOFISE2\n \nTABLESPACE_NAME CON_ID PDB_NAME\n------------------------------ ---------- ----------\nSYSTEM 4 HIPOFISE2\nTEMP 4 HIPOFISE2\nUNDOTBS1 4 HIPOFISE2\nUSERS 4 HIPOFISE2\n \n15 rows selected.\n<\/pre><\/div>\n\n\nSeguindo a mesma l\u00f3gica, a CDB_DATA_FILES retorna os datafiles do CDB$ROOT e seus 2 PDBS, exceto o SEED:<\/p>\n\n\n
\nSQL> col file_name format a50\nSQL> col tablespace_name format a8\nSQL> col file_id format 9999\nSQL> col con_id format 999\nSQL> SELECT FILE_NAME, TABLESPACE_NAME, FILE_ID, CON_ID FROM CDB_DATA_FILES ORDER BY CON_ID;\n \nFILE_NAME TABLESPA FILE_ID CON_ID\n-------------------------------------------------- -------- ------- ------\n\/oracle\/dados\/TALAMO\/datafile\/o1_mf_system_j3w17w2 SYSTEM 1 1\n6_.dbf\n \n\/oracle\/dados\/TALAMO\/datafile\/o1_mf_undotbs1_j3w1c UNDOTBS1 4 1\nyoh_.dbf\n \n\/oracle\/dados\/TALAMO\/datafile\/o1_mf_users_j3w1czy4 USERS 7 1\n_.dbf\n \n\/oracle\/dados\/TALAMO\/datafile\/o1_mf_sysaux_j3w1bkd SYSAUX 3 1\nm_.dbf\n \nFILE_NAME TABLESPA FILE_ID CON_ID\n-------------------------------------------------- -------- ------- ------\n \n\/oracle\/dados\/TALAMO\/BCF7B920CA5C126AE0536B00A8C0B SYSTEM 9 3\n4F1\/datafile\/o1_mf_system_j4b7tm5g_.dbf\n \n\/oracle\/dados\/TALAMO\/BCF7B920CA5C126AE0536B00A8C0B USERS 12 3\n4F1\/datafile\/o1_mf_users_j4b81g2r_.dbf\n \n\/oracle\/dados\/TALAMO\/BCF7B920CA5C126AE0536B00A8C0B UNDOTBS1 11 3\n4F1\/datafile\/o1_mf_undotbs1_j4b7tm63_.dbf\n \n\/oracle\/dados\/TALAMO\/BCF7B920CA5C126AE0536B00A8C0B SYSAUX 10 3\n \nFILE_NAME TABLESPA FILE_ID CON_ID\n-------------------------------------------------- -------- ------- ------\n4F1\/datafile\/o1_mf_sysaux_j4b7tm62_.dbf\n \n\/oracle\/dados\/TALAMO\/BD02D62DBD0C163FE0536B00A8C0E SYSTEM 13 4\nF43\/datafile\/o1_mf_system_j4cpg63g_.dbf\n \n\/oracle\/dados\/TALAMO\/BD02D62DBD0C163FE0536B00A8C0E USERS 16 4\nF43\/datafile\/o1_mf_users_j4cpgr31_.dbf\n \n\/oracle\/dados\/TALAMO\/BD02D62DBD0C163FE0536B00A8C0E UNDOTBS1 15 4\nF43\/datafile\/o1_mf_undotbs1_j4cpg640_.dbf\n \n \nFILE_NAME TABLESPA FILE_ID CON_ID\n-------------------------------------------------- -------- ------- ------\n\/oracle\/dados\/TALAMO\/BD02D62DBD0C163FE0536B00A8C0E SYSAUX 14 4\nF43\/datafile\/o1_mf_sysaux_j4cpg640_.dbf\n \n \n12 rows selected.\n<\/pre><\/div>\n\n\nMas caso usemos a DBA_DATA_FILES, apenas os datafiles do CDB$ROOT (que \u00e9 o current container) ser\u00e3o retornados:<\/p>\n\n\n
\nSQL> SELECT FILE_NAME, TABLESPACE_NAME, FILE_ID FROM DBA_DATA_FILES;\n \nFILE_NAME TABLESPA FILE_ID\n-------------------------------------------------- -------- -------\n\/oracle\/dados\/TALAMO\/datafile\/o1_mf_system_j3w17w2 SYSTEM 1\n6_.dbf\n \n\/oracle\/dados\/TALAMO\/datafile\/o1_mf_sysaux_j3w1bkd SYSAUX 3\nm_.dbf\n \n\/oracle\/dados\/TALAMO\/datafile\/o1_mf_users_j3w1czy4 USERS 7\n_.dbf\n \n\/oracle\/dados\/TALAMO\/datafile\/o1_mf_undotbs1_j3w1c UNDOTBS1 4\nyoh_.dbf\n \nFILE_NAME TABLESPA FILE_ID\n-------------------------------------------------- -------- -------\n<\/pre><\/div>\n\n\nOutra alternativa, \u00e9 usarmos as views V$TABLESPACE\/V$DATAFILE, que retornam as informa\u00e7\u00f5es do CDB$ROOT e seus PDBS, INCLUINDO o SEED PDB:<\/p>\n\n\n
\nSQL> col name format a12\nSQL> 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;\n \n FILE# NAME TS# CON_ID\n---------- ------------ ---------- ------\n 1 SYSTEM 0 1\n 3 SYSAUX 1 1\n 4 UNDOTBS1 2 1\n 7 USERS 4 1\n 5 SYSTEM 0 2\n 6 SYSAUX 1 2\n 8 UNDOTBS1 2 2\n 9 SYSTEM 0 3\n 10 SYSAUX 1 3\n 11 UNDOTBS1 2 3\n 12 USERS 5 3\n \n FILE# NAME TS# CON_ID\n---------- ------------ ---------- ------\n 13 SYSTEM 0 4\n 14 SYSAUX 1 4\n 15 UNDOTBS1 2 4\n 16 USERS 5 4\n \n15 rows selected.\n<\/pre><\/div>\n\n\nAlterando o container para o SEED e consultando seus datafiles, \u00e9 poss\u00edvel perceber que o OMF joga os arquivos dentro do mesmo diret\u00f3rio do CDB$ROOT:<\/p>\n\n\n
\nSQL> ALTER SESSION SET CONTAINER=PDB$SEED;\n \nSession altered.\n \nSQL> col file_name format a80\nSQL> SELECT FILE_NAME FROM DBA_DATA_FILES;\n \nFILE_NAME\n--------------------------------------------------------------------------------\n\/oracle\/dados\/TALAMO\/datafile\/o1_mf_system_j3w1q776_.dbf\n\/oracle\/dados\/TALAMO\/datafile\/o1_mf_sysaux_j3w1q76z_.dbf\n\/oracle\/dados\/TALAMO\/datafile\/o1_mf_undotbs1_j3w1q779_.dbf\n<\/pre><\/div>\n\n\nOutro comportamento a ser observado: quando logamos em um PDB e usamos uma view do tipo CDB, apenas as informa\u00e7\u00f5es daquele PDB \u00e9 retornada:<\/p>\n\n\n
\nSQL> ALTER SESSION SET CONTAINER=HIPOFISE1;\n \nSession altered.\n \nSQL> COL FILE_NAME FOR A50\nSQL> SELECT FILE_NAME,CON_ID FROM CDB_DATA_FILES;\n \nFILE_NAME CON_ID\n-------------------------------------------------- ------\n\/oracle\/dados\/TALAMO\/BCF7B920CA5C126AE0536B00A8C0B 3\n4F1\/datafile\/o1_mf_system_j4b7tm5g_.dbf\n \n\/oracle\/dados\/TALAMO\/BCF7B920CA5C126AE0536B00A8C0B 3\n4F1\/datafile\/o1_mf_sysaux_j4b7tm62_.dbf\n \n\/oracle\/dados\/TALAMO\/BCF7B920CA5C126AE0536B00A8C0B 3\n4F1\/datafile\/o1_mf_undotbs1_j4b7tm63_.dbf\n \n\/oracle\/dados\/TALAMO\/BCF7B920CA5C126AE0536B00A8C0B 3\n4F1\/datafile\/o1_mf_users_j4b81g2r_.dbf\n \nFILE_NAME CON_ID\n-------------------------------------------------- ------\n<\/pre><\/div>\n\n\nO mesmo conceito acontece com as views V$:<\/p>\n\n\n
\nSQL> SHOW CON_NAME CON_ID\n \nCON_NAME\n------------------------------\nHIPOFISE1\n \nCON_ID\n------------------------------\n3\nSQL> COL NAME FOR A50\nSQL> SELECT NAME,CON_ID FROM V$DATAFILE;\n \nNAME CON_ID\n-------------------------------------------------- ------\n\/oracle\/dados\/TALAMO\/BCF7B920CA5C126AE0536B00A8C0B 3\n4F1\/datafile\/o1_mf_system_j4b7tm5g_.dbf\n \n\/oracle\/dados\/TALAMO\/BCF7B920CA5C126AE0536B00A8C0B 3\n4F1\/datafile\/o1_mf_sysaux_j4b7tm62_.dbf\n \n\/oracle\/dados\/TALAMO\/BCF7B920CA5C126AE0536B00A8C0B 3\n4F1\/datafile\/o1_mf_undotbs1_j4b7tm63_.dbf\n \n\/oracle\/dados\/TALAMO\/BCF7B920CA5C126AE0536B00A8C0B 3\n4F1\/datafile\/o1_mf_users_j4b81g2r_.dbf\n \nNAME CON_ID\n-------------------------------------------------- ------\n<\/pre><\/div>\n\n\nObs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laborat\u00f3rio pessoal para fins de aprendizado.<\/p>\n","protected":false},"excerpt":{"rendered":"
Nesta nova arquitetura, a mudan\u00e7a \u00e9 transparente para quem vai consumir o banco de dados, mas em algumas tarefas de administra\u00e7\u00e3o do ambiente (na minha opini\u00e3o, principalmente de monitora\u00e7\u00e3o), h\u00e1 detalhes para considerar. Abordarei as consultas de tablespaces e datafiles neste artigo. Logado no CDB$ROOT, consultando as tablespaces padr\u00f5es permanente e tempor\u00e1ria: Consumindo a view […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[],"class_list":["post-2759","post","type-post","status-publish","format-standard","hentry","category-multitenant"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/2759","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=2759"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/2759\/revisions"}],"predecessor-version":[{"id":9226,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/2759\/revisions\/9226"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=2759"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=2759"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=2759"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}