Search notes:

Oracle: V$MEMORY_DYNAMIC_COMPONENTS

v$memory_dynamic_components shows the current sizes of all dynamically tuned memory components, including the total size of the SGA and the instance PGA.

v$memory_dynamic_components shows exactly the same information as v$sga_dynamic_components except that v$sga_dynamic_components does not include the two rows for SGA Target and PGA Target).

Both views select from x$kmgsct.

select
  component,
  round(        current_size /1024/1024, 1)     curr_size_mb,
  round(            min_size /1024/1024, 1)      min_size_mb,
  round(            max_size /1024/1024, 1)      max_size_mb,
  round( user_specified_size /1024/1024, 1)     spec_size_mb,
  oper_count,
  last_oper_type,
  last_oper_mode,
  round(((sysdate - last_oper_time) * 24 * 60)) last_oper_minutes_ago,
  round(        granule_size /1024/1024, 1)     gran_size_mb
from
  v$memory_dynamic_components
order by
  lower(component);

Github repository oracle-patterns, path: /Installed/dynamic-performance-views/memory/dynamic_components/show.sql

Determine diff between SGA target and actual SGA size:

select
   (select current_size from v$memory_dynamic_components where component = 'SGA Target') -
   (select sum(value)   from v$sga                                                     ) diff_sga_target_to_sga,
    --
   (select current_size from v$memory_dynamic_components where component = 'SGA Target') -
   (select    value     from v$parameter                 where name      = 'sga_target') diff_to_param
from
   dual;

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1758204980, '216.73.216.150', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/installed/dynamic-performance-views/memory/dynamic_components/index(81): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78

Oracle: V$MEMORY_DYNAMIC_COMPONENTS

See also