溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
在oracle 12c多租戶環境里，我們有時想通過cdb_data_files來查看種子數據庫(pdb$seed)的文件信息，會發現沒有反饋結果，但你可能會發現網上有些案例里卻能通過cdb_data_files這個視圖查看到關于pdb$seed的信息。這是怎么回事呢？
我在12c幾個版本的reference里沒有找到關于cdb_data_files的詳細說明，不過在 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.
這說明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 * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0 PL/SQL Release 12.1.0.1.0 - Production 0 CORE 12.1.0.1.0 Production 0 TNS for Linux: Version 12.1.0.1.0 - Production 0 NLSRTL Version 12.1.0.1.0 - Production 0 SQL> 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.dbf 10 rows selected.
SQL> select name,value,description from v$parameter where name like '%seed%'; no rows selected SQL> 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
2. 不過在12.1.0.2版本里，oracle引入了參數EXCLUDE_SEED_CDB_VIEW來控制查看pdb$seed
我們可以看到，默認情況下是查詢不到pdb$seed的
SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0 PL/SQL Release 12.1.0.2.0 - Production 0 CORE 12.1.0.2.0 Production 0 TNS for Linux: Version 12.1.0.2.0 - Production 0 NLSRTL Version 12.1.0.2.0 - Production 0 SQL> 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.dbf 8 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
參數exclude_seed_cdb_view默認值為true，當設置為false時，就能查看到pdbs$seed了
SQL> alter system set exclude_seed_cdb_view=FALSE; System altered. SQL> select file_name from cdb_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 10 rows selected.
3. 而在新版本12.2.0.1中，oracle已經把 EXCLUDE_SEED_CDB_VIEW作為隱含參數了，默認值同樣為TURE。
SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 PL/SQL Release 12.2.0.1.0 - Production 0 CORE 12.2.0.1.0 Production 0 TNS for Linux: Version 12.2.0.1.0 - Production 0 NLSRTL Version 12.2.0.1.0 - Production 0 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 8 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
不過好在這個參數可以在instance或session級別修改。
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.dbf 11 rows selected.
從上面的實驗可以看到，oracle越來越重視對pdb$seed的保護。從第一版全部開放，再到12.1.0.2引入普通參數進行查詢控制，最后干脆改為隱含參數。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
