3 Replies Latest reply on Mar 21, 2019 3:08 AM by Manu Alphonse

    Effect of modifying init parameters from PDBs

    Y.Ramlet

      Oracle DB version: 12.2

      OS : Oracle Linux 7.6

       

      I have a question on the effect of modifying init.ora parameters while logged in to PDB .

       

      Demo below:

      Initially at Container DB level , the SGA_TARGET was set to 2688 MB and DB_CACHE_SIZE was set to 0 MB (Exhibit1)

      Then I logged into a PDB using ALTER SESSION SET CONTAINER and modified SGA_TARGET to 2000MB and DB_CACHE_SIZE to 200MB (Exhibit2)

       

      As you can see below in Exhibit3 , when I log into container DB , the values remain unchanged.

       

      So, what does this all mean ? Does this mean PDB3 now has dedicated 2000MB of SGA_TARGET and 200MB of DB_CACHE_SIZE ?

       

       

      Exhibit1
      --- Connected to container DB(root)
      SQL> show parameter db_cache_size
      
      NAME                     TYPE     VALUE
      ------------------------------------ ----------- ------------------------------
      db_cache_size                 big integer 0
      SQL>
      SQL>
      SQL> show parameter sga_target
      
      NAME                     TYPE     VALUE
      ------------------------------------ ----------- ------------------------------
      sga_target                 big integer 2688M
      SQL>
      SQL>
      SQL>
      SQL> show con_id
      
      CON_ID
      ------------------------------
      1
      SQL>
      
      Exhibit2
      --- Connecting to a PDB and modifying SGA_TARGET and DB_CACHE_SIZE
      
      
      SQL> alter session set container=PDB3;
      
      Session altered.
      
      SQL> alter system set db_cache_size=200m;
      
      System altered.
      
      SQL> alter system set sga_target=2000m;
      
      System altered.
      
      SQL> show con_id
      
      CON_ID
      ------------------------------
      3
      SQL> show parameter db_cache_s
      
      NAME                     TYPE     VALUE
      ------------------------------------ ----------- ------------------------------
      db_cache_size                 big integer 200M
      SQL>
      SQL>
      SQL> show parameter sga_t
      
      NAME                     TYPE     VALUE
      ------------------------------------ ----------- ------------------------------
      sga_target                 big integer 2000M
      SQL>
      
      
      Exhibit3
      --- exited from the above terminal and logging in to container DB(root) again
      --- The values seem unchanged (same as Exhibit 1) when I check from Container DB(root) level
      
      $ sqlplus / as sysdba
      
      SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 5 11:58:43 2019
      
      Copyright (c) 1982, 2016, Oracle.  All rights reserved.
      
      
      Connected to:
      Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
      
      SQL> show parameter sga_t
      
      NAME                     TYPE     VALUE
      ------------------------------------ ----------- ------------------------------
      sga_target                 big integer 2688M
      SQL>
      SQL>
      SQL> show parameter db_cache_size
      
      NAME                     TYPE     VALUE
      ------------------------------------ ----------- ------------------------------
      db_cache_size                 big integer 0