在oracle 12c多租户环境里，我们有时想通过cdb_data_files来查看种子数据库(pdb$seed)的文件信息，会发现没有反馈结果，但你可能会发现网上有些案例里却能通过cdb_data_files这个视图查看到关于pdb$seed的信息。这是怎么回事呢？
我在12c几个版本的reference里没有找到关于cdb_data_files的详细说明，不过在 Administrator Guide中找到这句话：
theDBA_PDBSandCDB_DATA_FILESviews to show the name and location of each data file for all of the PDBs in a CDB,including the CDB seed.
这说明cdb_data_files确实有pdb$seed的信息。
在MOS中，文档(1940806.1) PDB$SEED Datafiles Not Appear In CDB_DATA_FILES介绍是参数EXCLUDE_SEED_CDB_VIEW 的设置问题。不过对于这个新参数，oracle 12c各个版本的情况都不相同。
1. 首先在oracle 12.1.0.1版本中，没有参数EXCLUDE_SEED_CDB_VIEW。因此，你会发现在这个版本下，你能从cdb_data_files中直接查看到pdb$seed。
sql>select*fromv$version; BANNER CON_ID ------------------------------------------------------------------------------------------ OracleDatabase12cEnterpriseEditionRelease12.1.0.1.0-64bitProduction 0 PL/sqlRelease12.1.0.1.0-Production 0 CORE 12.1.0.1.0 Production 0 TNSforLinux:Version12.1.0.1.0-Production 0 NLSRTLVersion12.1.0.1.0-Production 0 sql>selectfile_namefromcdb_data_files; FILE_NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /u01/dbdata/cdb1/system01.dbf /u01/dbdata/cdb1/sysaux01.dbf /u01/dbdata/cdb1/undotbs01.dbf /u01/dbdata/cdb1/users01.dbf /u01/dbdata/cdb1/pdbseed/system01.dbf /u01/dbdata/cdb1/pdbseed/sysaux01.dbf /u01/dbdata/cdb1/pdb1/system01.dbf /u01/dbdata/cdb1/pdb1/sysaux01.dbf /u01/dbdata/cdb1/pdb1/SAMPLE_SCHEMA_users01.dbf /u01/dbdata/cdb1/pdb1/example01.dbf 10rowsselected.
sql>selectname,value,descriptionfromv$parameterwherenamelike'%seed%'; norowsselected sql>SELECTksppinm,ksppstvl,ksppdescFROMx$ksppix,x$ksppcvyWHEREx.indx=y.indxANDksppinmlike'%seed%' KSPPINM KSPPSTVL KSPPDESC -------------------------------------------------------------------------------------------------------------- _deferred_seg_in_seed TRUE EnableDeferredSegmentCreationinSeed
2. 不过在12.1.0.2版本里，oracle引入了参数EXCLUDE_SEED_CDB_VIEW来控制查看pdb$seed
我们可以看到，默认情况下是查询不到pdb$seed的
sql>select*fromv$version; BANNER CON_ID ------------------------------------------------------------------------------------------ OracleDatabase12cEnterpriseEditionRelease12.1.0.2.0-64bitProduction 0 PL/sqlRelease12.1.0.2.0-Production 0 CORE 12.1.0.2.0 Production 0 TNSforLinux:Version12.1.0.2.0-Production 0 NLSRTLVersion12.1.0.2.0-Production 0 sql>selectfile_namefromcdb_data_files; FILE_NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /u01/dbdata/cdb2/system01.dbf /u01/dbdata/cdb2/sysaux01.dbf /u01/dbdata/cdb2/undotbs01.dbf /u01/dbdata/cdb2/users01.dbf /u01/dbdata/cdb2/pdb2/system01.dbf /u01/dbdata/cdb2/pdb2/sysaux01.dbf /u01/dbdata/cdb2/pdb2/SAMPLE_SCHEMA_users01.dbf /u01/dbdata/cdb2/pdb2/example01.dbf 8rowsselected.
sql>SELECTksppinm,x$ksppcvyWHEREx.indx=y.indxANDksppinmlike'%seed_cdb%'; KSPPINM KSPPSTVL KSPPDESC -------------------------------------------------------------------------------------------------------------- exclude_seed_cdb_view TRUE excludePDB$SEEDfromCDBViewResult
参数exclude_seed_cdb_view默认值为true，当设置为false时，就能查看到pdbs$seed了
sql>altersystemsetexclude_seed_cdb_view=FALSE; Systemaltered. sql>selectfile_namefromcdb_data_files; FILE_NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /u01/dbdata/cdb2/pdb2/system01.dbf /u01/dbdata/cdb2/pdb2/sysaux01.dbf /u01/dbdata/cdb2/pdb2/SAMPLE_SCHEMA_users01.dbf /u01/dbdata/cdb2/pdb2/example01.dbf /u01/dbdata/cdb2/system01.dbf /u01/dbdata/cdb2/sysaux01.dbf /u01/dbdata/cdb2/undotbs01.dbf /u01/dbdata/cdb2/users01.dbf /u01/dbdata/cdb2/pdbseed/system01.dbf /u01/dbdata/cdb2/pdbseed/sysaux01.dbf 10rowsselected.
3. 而在新版本12.2.0.1中，oracle已经把EXCLUDE_SEED_CDB_VIEW作为隐含参数了，默认值同样为TURE。
sql>select*fromv$version; BANNER CON_ID ------------------------------------------------------------------------------------------ OracleDatabase12cEnterpriseEditionRelease12.2.0.1.0-64bitProduction 0 PL/sqlRelease12.2.0.1.0-Production 0 CORE 12.2.0.1.0 Production 0 TNSforLinux:Version12.2.0.1.0-Production 0 NLSRTLVersion12.2.0.1.0-Production 0 sql>selectfile_namefromcdb_data_files; FILE_NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /u01/dbdata/cdb3/system01.dbf /u01/dbdata/cdb3/sysaux01.dbf /u01/dbdata/cdb3/undotbs01.dbf /u01/dbdata/cdb3/users01.dbf /u01/dbdata/cdb3/orclpdb/system01.dbf /u01/dbdata/cdb3/orclpdb/sysaux01.dbf /u01/dbdata/cdb3/orclpdb/undotbs01.dbf /u01/dbdata/cdb3/orclpdb/users01.dbf 8rowsselected.
sql>SELECTksppinm,x$ksppcvyWHEREx.indx=y.indxANDksppinmlike'%seed_cdb%'; KSPPINM KSPPSTVL KSPPDESC -------------------------------------------------------------------------------------------------------------- _exclude_seed_cdb_view TRUE excludePDB$SEEDfromCDBViewResult
不过好在这个参数可以在instance或session级别修改。
sql>altersystemset"_exclude_seed_cdb_view"=FALSE; Systemaltered. sql>altersessionset"_exclude_seed_cdb_view"=FALSE; Sessionaltered.
sql>selectfile_namefromcdb_data_files; FILE_NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /u01/dbdata/cdb3/system01.dbf /u01/dbdata/cdb3/sysaux01.dbf /u01/dbdata/cdb3/undotbs01.dbf /u01/dbdata/cdb3/users01.dbf /u01/dbdata/cdb3/orclpdb/system01.dbf /u01/dbdata/cdb3/orclpdb/sysaux01.dbf /u01/dbdata/cdb3/orclpdb/undotbs01.dbf /u01/dbdata/cdb3/orclpdb/users01.dbf /u01/dbdata/cdb3/pdbseed/system01.dbf /u01/dbdata/cdb3/pdbseed/sysaux01.dbf /u01/dbdata/cdb3/pdbseed/undotbs01.dbf 11rowsselected.
从上面的实验可以看到，oracle越来越重视对pdb$seed的保护。从第一版全部开放，再到12.1.0.2引入普通参数进行查询控制，最后干脆改为隐含参数。
原文链接：https://www.f2er.com/oracle/208738.html