Database Software

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