[2020-12-25] CDB下面查看哪些PDB修改了参数

--//在PDB下面修改参数

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
SQL> alter session set container=PDB1;

Session altered.

SQL> alter system set open_cursors=400;

System altered.

SQL> show parameter open_cursors

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
open_cursors                         integer                400
SQL> 

--//在PDB下面创建pfile可以看到已经修改过的参数

SQL> create pfile='/tmp/pfile.txt' from spfile;

File created.

SQL> !cat /tmp/pfile.txt
*.inmemory_size=209715200
*.open_cursors=400
*.remote_listener='dm-scan:1521'

SQL>

--//在CDB模式下查看哪些PDB修改了参数

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> 


SQL> select p.name, s.name,s.value$ from v$pdbs p, pdb_spfile$ s where p.con_uid = s.pdb_uid;

NAME                 NAME                 VALUE$
-------------------- -------------------- ------------------------------
PDB1                 open_cursors         400
PDB1                 remote_listener      'dm-scan:1521'
PDB1                 inmemory_size        209715200
SQL> 

--//把PDB下面的参数还原

SQL> alter session set container=PDB1;

Session altered.

SQL> alter system reset open_cursors sid='*' scope=spfile;

System altered.

SQL> show parameter open_cursors

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
open_cursors                         integer                400
SQL> 

--//发现如果是reset的话，不会直接生效，需要重启pdb

SQL> alter pluggable database PDB1 close;

Pluggable database altered.

SQL>  alter pluggable database PDB1 open;

Pluggable database altered.

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> show parameter open_cursor

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
open_cursors                         integer                300    --//和CDB下面的值一样
SQL>