Playing with Oracle 12c

Parameters - Oracle 12c container database has some changes when we talk about parameters. For example, there is no SPFILE for a pluggable database!

[oracle@prac12c dbs]$ cd $ORACLE_HOME/dbs
[oracle@prac12c dbs]$ ls
  hc_FUSCDB.dat  initFUSCDB.ora  init.ora  lkFUSCDB  orapwFUSCDB  spfileFUSCDB.ora


Trying to change the parameter open_cursors within PDB, this change will have no impact in the CDB -

Logon to CDB and verify the value:

SQL> sho con_name  

CON_NAME

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

CDB$ROOT

SQL> sho parameter open_cursors

NAME                                 TYPE        VALUE

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

open_cursors                         integer     300

Logging into PDB -

SQL> conn sys@PDB01 as sysdba

Enter password: 

Connected.

SQL> 

SQL> sho con_name

CON_NAME

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

PDB01

SQL> sho parameter open_cursor

NAME                                 TYPE        VALUE

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

open_cursors                         integer     300

SQL> 

Try changing this value in PDB at SPfile and Memory level and you will notice that value at root will still be the original value. However the question remains as to where the changed value is stored as there is no SPfile for PDB.

SQL> alter system set open_cursors=800 scope=BOTH;

System altered.

SQL> sho con_name

CON_NAME

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

PDB01

SQL> sho parameter open_cursor

NAME                                 TYPE        VALUE

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

open_cursors                         integer     800

SQL> conn / as sysdba

Connected.

SQL> sho con_name

CON_NAME

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

CDB$ROOT

SQL> sho parameter open_cursor

NAME                                 TYPE        VALUE

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

open_cursors                         integer     300

Changed values are stored in v$system_parameter -

SQL> select name, con_id, value from v$system_parameter where name='open_cursors';

NAME                                                                                 CON_ID

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

VALUE

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

open_cursors                                                                              0

300

open_cursors                                                                              3

800