About how to view the datafile of seed pdb in the CDB_DATA_FILES view

In the oracle 12c multi-tenant environment, we sometimes want to view the file information of the seed database (pdb$seed) through cdb_data_files, and we will find that there is no feedback, but you may find that some cases on the Internet can see the information about pdb$seed through the view of cdb_data_files. What's going on?

I didn't find a detailed description of cdb_data_files in several versions of 12c reference, but I found this sentence in Administrator Guide:

The DBA_PDBS and CDB_DATA_FILES views to show the name and location of each data file for all of the PDBs in a CDB, including the CDB seed.

This shows that cdb_data_files does have information about pdb$seed.

In MOS, the document (1940806.1) PDB$SEED Datafiles Not Appear In CDB_DATA_FILES introduction is a matter of setting the parameter EXCLUDE_SEED_CDB_VIEW. However, the situation varies from version to version of oracle 12c for this new parameter.

1. First of all, in oracle 12.1.0.1, there is no parameter EXCLUDE_SEED_CDB_VIEW. Therefore, you will find that in this version, you can view pdb$seed directly from cdb_data_files.

SQL > select * from v$version BANNER CON_ID -Oracle Database 12c Enterprise Edition Release 12.1.0.1.0-64bit Production 0PL/SQL Release 12.1.0.1.0-Production 0CORE 12.1.0.1.0 Production 0TNS for Linux: Version 12.1.0.1.0-Production 0NLSRTL Version 12.1.0.1.0-Production 0SQL > select file_name from cdb_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.dbf10 rows selected.SQL > select name Value,description from v$parameter where name like'% seed%' No rows selectedSQL > SELECT ksppinm, ksppstvl, ksppdesc FROM x$ksppi x X$ksppcv y WHERE x.indx = y.indx AND ksppinm like'% seed%'KSPPINM KSPPSTVL KSPPDESC -- _ deferred_seg_in_seed TRUE Enable Deferred Segment Creation in Seed

two。 However, in version 12.1.0.2, oracle introduced the parameter EXCLUDE_SEED_CDB_VIEW to control the viewing of pdb$seed.

As we can see, pdb$seed cannot be queried by default.

SQL > select * from v$version BANNER CON_ID -- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit Production 0PL/SQL Release 12.1.0.2.0-Production 0CORE 12.1.0.2.0 Production 0TNS for Linux: Version 12.1.0.2.0-Production 0NLSRTL Version 12.1.0.2.0-Production 0SQL > select file_name from cdb_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.dbf8 rows selected.SQL > SELECT ksppinm Ksppstvl, ksppdesc FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND ksppinm like'% seed_cdb%' KSPPINM KSPPSTVL KSPPDESC -exclude_seed_cdb_view TRUE exclude PDB$SEED from CDB View Result

The default value of parameter exclude_seed_cdb_view is true. When set to false, you can view pdbs$seed.

SQL > alter system set exclude_seed_cdb_view=FALSE;System altered.SQL > select file_name from cdb_data_files FILE_NAME- -/ u01ax dbdataUniverse cdb2Universe system 01.dbfqp01.dbdataqqcdb2According to sysaux01.dbffAccording to u01UniUnix01.dbffqqu01CdbdataAccording to pdb2Lash cdb2Lash pdb2Unix SAMPLEsteps 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.dbf10 rows selected.

3. In the new version 12.2.0.1, oracle already takes EXCLUDE_SEED_CDB_VIEW as an implicit parameter, and the default value is also TURE.

SQL > select * from v$version BANNER CON_ID -Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Production 0PL/SQL Release 12.2.0.1.0-Production 0CORE 12.2.0.1.0 Production 0TNS for Linux: Version 12.2.0.1.0-Production 0NLSRTL Version 12.2.0.1.0-Production 0SQL > select file_name from cdb_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.dbf8 rows selected.SQL > SELECT ksppinm Ksppstvl, ksppdesc FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND ksppinm like'% seed_cdb%' KSPPINM KSPPSTVL KSPPDESC -_ exclude_seed_cdb_view TRUE exclude PDB$SEED from CDB View Result

Fortunately, this parameter can be modified at the instance or session level.

SQL > alter system set "_ exclude_seed_cdb_view" = FALSE;System altered.SQL > alter session set "_ exclude_seed_cdb_view" = FALSE;Session altered.SQL > select file_name from cdb_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.dbf11 rows selected.

From the above experiments, we can see that oracle pays more and more attention to the protection of pdb$seed. From the first version to 12.1.0.2, ordinary parameters are introduced for query control, and finally the implicit parameters are changed to implicit parameters.