Chanding Parameter in PDB.

Chanding Parameter in PDB.

a) There is only a Single spfile for the CDB.

b) PDB parameter changes are loaded in memory when PDB is closed / Stored in data dictionary when CDB is closed.

c) Only the parameters can be modified for which ispdb_modifiable is true in V$parameter.

SQL> select con_id, name, open_mode from V$pdbs;

    CON_ID NAME                           OPEN_MODE

---------- ------------------------------ ----------

         2 PDB$SEED                       READ ONLY

         3 PDB_PURCHASE                   MOUNTED

         4 PDB_HR                         MOUNTED

SQL> select name, value , ISPDB_MODIFIABLE from V$parameter where name = 'recyclebin';

NAME                           VALUE                                    ISPDB

------------------------------ ---------------------------------------- -----

recyclebin                     on                                       TRUE

SQL> select name, value , ISPDB_MODIFIABLE from V$parameter where name like '%ddl%';

NAME                           VALUE                                    ISPDB

------------------------------ ---------------------------------------- -----

ddl_lock_timeout               0                                        TRUE

enable_ddl_logging             FALSE                                    TRUE

SQL> show con_name;

CON_NAME

------------------------------

CDB$ROOT

SQL> alter session set container=pdb_hr;

Session altered.

SQL> show parameter recy;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

recyclebin                           string      on

SQL> alter system set recyclebin = off;

alter system set recyclebin = off

                                *

ERROR at line 1:

ORA-02096: specified initialization parameter is not modifiable with this option

SQL> alter system set recyclebin = off scope=spfile;

System altered.

-- Parameter change will take effect only when the pdb is closed and started again.

SQL> alter pluggable database pdb_hr close;

Pluggable database altered.

SQL> alter pluggable database pdb_hr open;

Pluggable database altered.

SQL> show parameter recycleb;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

recyclebin                           string      OFF

SQL> alter session set container=pdb_hr;

Session altered.

SQL> alter system set ddl_lock_timeout = 60;

System altered.

SQL> show parameter ddl_lock;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

ddl_lock_timeout                     integer     60

SQL>

--Parameter value taken effect immediately.

SQL> alter session set container=cdb$root;

Session altered.

Lit of parameters which are changed in PDB_HR

SQL> select a.PDB_UID, b.name, a.name, a.value$ from pdb_spfile$ a, v$pdbs b where a.PDB_UID= b.CON_UID and b.name = 'PDB_HR';

   PDB_UID NAME                           NAME                           VALUE$

---------- ------------------------------ ------------------------------ ----------------------------------------

1593306118 PDB_HR                         ddl_lock_timeout               60

1593306118 PDB_HR                         recyclebin                     'OFF'

SQL> select con_id, name, value from V$system_parameter where name = ‘ddl_lock_timeout’;

CON_ID      NAME        VALUE

———- ——————————  —————————————-

0     ddl_lock_timeout 30

4     ddl_lock_timeout 60

SQL> select con_id, name, open_mode from V$pdbs;

CON_ID      NAME        OPEN_MODE

———- —————————— ———-

2     PDB$SEED    READ ONLY

3     PDB_PURCHASE      MOUNTED

4     PDB_HR            READ WRITE

SQL> alter session set container=PDB_PURCHASE;

Session altered.

SQL> alter pluggable database PDB_PURCHASE open;

Pluggable database altered.

SQL> show con_name;

CON_NAME

——————————

PDB_PURCHASE

SQL> alter system set ddl_lock_timeout=40 ;

System altered.

SQL> conn / as sysdba

Connected.

SQL> select con_id, name, value from V$system_parameter where name = ‘ddl_lock_timeout’;

CON_ID NAME             VALUE

———-   ——————————       —————————————-

0      ddl_lock_timeout 30

3      ddl_lock_timeout 40

4      ddl_lock_timeout 60