Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, prst.

Asked: July 14, 2022 - 4:52 am UTC

Last updated: July 26, 2022 - 7:18 am UTC

Version: 19c

Viewed 10K+ times! This question is

You Asked

Hi,

I have just finished upgrading 11g to 19c.

When I do query "
SELECT name,value,display_value,isdefault,isbasic FROM V$PARAMETER WHERE name IN ('memory_target', 'sga_target', 'pga_aggregate_target');
", the result is as follows :

SGA_TARGET = 0

MEMORY_TARGET = 0

PGA_AGGREGATE_TARGET = 30 GB.

What can I tell about these memory values ?

Is this telling me that AMM is being used ? What I know, for manual management, all values must be 0.
If this is the configuration for the pluggable db, how do I log in into root container ?
which configuration is used, the pluggable db or the root container ?

thanks

and Connor said...

You'll need to be on the root to see the true values for some parameters, eg

--
-- in pluggable
--
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------
allow_group_access_to_sga            boolean     FALSE
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 3G
sga_min_size                         big integer 0
sga_target                           big integer 0
unified_audit_sga_queue_size         integer     1048576

--
-- in root
--
SQL>  conn / as sysdba
Connected.
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------
allow_group_access_to_sga            boolean     FALSE
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 3G
sga_min_size                         big integer 0
sga_target                           big integer 3G <<==============
unified_audit_sga_queue_size         integer     1048576


Then the normal rules apply, ie, is memory_target set, is sga_target set, etc

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

adjee, July 21, 2022 - 2:58 am UTC

Hi Tom,

So you are saying that the parameter from pluggable database is not really accurate ? What about if there are more than 1 databases, how is the memory usage handled by the system ?

Thanks
Connor McDonald
July 26, 2022 - 7:18 am UTC

It is not a case of accuracy but security/assignment of roles. In a PDB, the concept of sga target does not really have any meaning, because it is a global structure. It makes sense for a pluggable to have a limit (sga_max_size) but the concept of a *target* only applies to the entire database.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database