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:
[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
