39 - [Oracle 19C] CONTAINER clause in alter system

 

ISSES_MODIFIABLE is false that means you cannot change it in the session, but you can change it.

ISSYS_MODIFIABLE(system modifiable) is immediate. This means that I can change the value in the memory level.

                                    FALSE: You can only change the value with a static parameter using scope=spfile.

                                                 In other words, the change will take effect from the next restart of the database.

                                    IMMEDIATE: If you change the value, it will take effect immediately from the current session.
                                    DEFERRED: The changed value will affect sessions connected thereafter.

ISPDB_MODIFIABLE(TRUE) this mean PDBs inherit initialization parameter values from the root

ISDEFAULT is false. This means that this value mentioned in the SP file, you can go to the SP file and you will find this is already in the SP file.

                                    (TRUE) Indicates whether the parameter is set to the default value.

                                    (FALSE) the parameter value was specified in the parameter file.

 

The database sets the value of the ISDEFAULT column to TRUE for parameters that are not specified in the init.ora or server parameter file (SPFILE).

 

 

• CONTAINER clause in alter system

- You can specify the CONTAINER clause when you set a parameter value in a CDB.

- A CDB uses an inheritance model for initialization parameters in which PDBs inherit initialization parameter values from the root

- A PDB can override the root's setting for some parameters(ISPDB_MODIFIABLE is TRUE)
Note: when the PDB override the root's setting, a new record will be added to V$system_PARAMETER

- If you specify CONTAINER = ALL, then the parameter setting applies to all containers in the CDB, including the root and all of the PDBs. The current container must be the root.

- If you specify CONTAINER = CURRENT, then the parameter setting applies only to the current container. But When the current container is the root, the parameter setting applies to the root and to any PDB

with an inheritance property of true for the parameter(no override done before by the pluggable)

- if you omit this clause, then CONTAINER = CURRENT is the default.

 

 

 

SQL> show con_name;
CON_NAME 
------------------------------
CDB$ROOT


SQL> select name,value,isses_modifiable , issys_modifiable , ispdb_modifiable ,ISDEFAULT,con_id
from V$system_PARAMETER where name='open_cursors';

NAME		     VALUE		  ISSES_MODIFIABLE     ISSYS_MODIFIABLE     ISPDB_MODIFIABLE	 ISDEFAULT     CON_ID
-------------------- -------------------- -------------------- -------------------- -------------------- --------- ----------
open_cursors	     301		  FALSE 	       IMMEDIATE	    TRUE		 FALSE		    0


SQL> alter system set open_cursors=400 container=current;
System altered.


SQL> select name,value,isses_modifiable , issys_modifiable , ispdb_modifiable ,ISDEFAULT,con_id
from V$system_PARAMETER where name='open_cursors';

NAME		     VALUE		  ISSES_MODIFIABLE     ISSYS_MODIFIABLE     ISPDB_MODIFIABLE	 ISDEFAULT     CON_ID
-------------------- -------------------- -------------------- -------------------- -------------------- --------- ----------
open_cursors	     400		  FALSE 	       IMMEDIATE	    TRUE		 FALSE		    0


SQL> alter session set container=orclpdb;
Session altered.


SQL> show con_name
CON_NAME
------------------------------
ORCLPDB


SQL> select name,value,isses_modifiable , issys_modifiable , ispdb_modifiable ,ISDEFAULT,con_id
from V$system_PARAMETER where name='open_cursors';

NAME		     VALUE		  ISSES_MODIFIABLE     ISSYS_MODIFIABLE     ISPDB_MODIFIABLE	 ISDEFAULT     CON_ID
-------------------- -------------------- -------------------- -------------------- -------------------- --------- ----------
open_cursors	     400		  FALSE 	       IMMEDIATE	    TRUE		 FALSE		    0


SQL> alter system set open_cursors=500 container=current;
System altered.


SQL> select name,value,isses_modifiable , issys_modifiable , ispdb_modifiable ,ISDEFAULT,con_id
from V$system_PARAMETER where name='open_cursors';

NAME		     VALUE		  ISSES_MODIFIABLE     ISSYS_MODIFIABLE     ISPDB_MODIFIABLE	 ISDEFAULT     CON_ID
-------------------- -------------------- -------------------- -------------------- -------------------- --------- ----------
open_cursors	     500		  FALSE 	       IMMEDIATE	    TRUE		 FALSE		    3


SQL> alter session set container=cdb$root;
Session altered.


SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT


SQL> select name,value,isses_modifiable , issys_modifiable , ispdb_modifiable ,ISDEFAULT,con_id
from V$system_PARAMETER where name='open_cursors';

NAME		     VALUE		  ISSES_MODIFIABLE     ISSYS_MODIFIABLE     ISPDB_MODIFIABLE	 ISDEFAULT     CON_ID
-------------------- -------------------- -------------------- -------------------- -------------------- --------- ----------
open_cursors	     400		  FALSE 	       IMMEDIATE	    TRUE		 FALSE		    0
open_cursors	     500		  FALSE 	       IMMEDIATE	    TRUE		 FALSE		    3


SQL> alter system set open_cursors=410 container=current;
System altered.


SQL> select name,value,isses_modifiable , issys_modifiable , ispdb_modifiable ,ISDEFAULT,con_id
from V$system_PARAMETER where name='open_cursors';

NAME		     VALUE		  ISSES_MODIFIABLE     ISSYS_MODIFIABLE     ISPDB_MODIFIABLE	 ISDEFAULT     CON_ID
-------------------- -------------------- -------------------- -------------------- -------------------- --------- ----------
open_cursors	     410		  FALSE 	       IMMEDIATE	    TRUE		 FALSE		    0
open_cursors	     500		  FALSE 	       IMMEDIATE	    TRUE		 FALSE		    3


SQL> alter system set open_cursors=410 container=all;
System altered.


SQL> select name,value,isses_modifiable , issys_modifiable , ispdb_modifiable ,ISDEFAULT,con_id
from V$system_PARAMETER where name='open_cursors';

NAME		     VALUE		  ISSES_MODIFIABLE     ISSYS_MODIFIABLE     ISPDB_MODIFIABLE	 ISDEFAULT     CON_ID
-------------------- -------------------- -------------------- -------------------- -------------------- --------- ----------
open_cursors	     410		  FALSE 	       IMMEDIATE	    TRUE		 FALSE		    0