Oracle Version :- 11g
SQL> select value from v$parameter where upper(name)='MEMORY_TARGET';
VALUE
--------------------------------------------------------------------------------
415236096
SQL> select value/1024/1024 mb
2 from v$parameter where upper(name) = 'SGA_MAX_SIZE';
MB
----------
396
-- size taken by a memory component
SQL> select pool, round(sum(BYTES)/1024/1024) MB
2 from V$SGASTAT
3 group by pool;
POOL MB
------------ ----------
98
java pool 4
streams pool 4
shared pool 148
large pool 4
SQL> select name , value/1024/1024 MB
2 from v$parameter
3 where upper(name) in (
4 'DB_CACHE_SIZE','DB_RECYCLE_CACHE_SIZE','DB_KEEP_CACHE_SIZE', '
5 DB_2k_CACHE_SIZE', 'DB_8k_CACHE_SIZE', 'DB_16k_CACHE_SIZE');
NAME MB
------------------------------ ----------
db_cache_size 0
db_keep_cache_size 0
db_recycle_cache_size 0
--For 10g
SQL> SELECT COMPONENT , CURRENT_SIZE/1024/1024 MB
2 FROM V$SGA_DYNAMIC_COMPONENTS
3 WHERE CURRENT_SIZE <>0;
COMPONENT MB
---------------------------------------------------------------- ----------
shared pool 148
large pool 4
java pool 4
streams pool 4
DEFAULT buffer cache 92
-- Oracle 11g
SQL> SELECT COMPONENT, ROUND(CURRENT_SIZE/1024/1024) CURRENT_SIZE ,
2 ROUND(MIN_SIZE/1024/1024) MIN, ROUND(MAX_SIZE/1024/1024) MAX
3 FROM V$MEMORY_DYNAMIC_COMPONENTS;
COMPONENT CURRENT_SIZE MIN MAX
---------------------------------------------------------------- ------------ ---------- ----------
shared pool 148 148 148
large pool 4 4 4
java pool 4 4 4
streams pool 4 4 4
SGA Target 260 260 260
DEFAULT buffer cache 92 92 92
KEEP buffer cache 0 0 0
RECYCLE buffer cache 0 0 0
DEFAULT 2K buffer cache 0 0 0
DEFAULT 4K buffer cache 0 0 0
DEFAULT 8K buffer cache 0 0 0
COMPONENT CURRENT_SIZE MIN MAX
---------------------------------------------------------------- ------------ ---------- ----------
DEFAULT 16K buffer cache 0 0 0
DEFAULT 32K buffer cache 0 0 0
Shared IO Pool 0 0 0
PGA Target 136 136 136
ASM Buffer Cache 0 0 0
-- To findout how Oracle has modified the memory area sizes by time
SQL> select START_TIME, END_TIME, STATUS, COMPONENT, OPER_TYPE, OPER_MODE,
PARAMETER, INITIAL_SIZE/1024/1024 INITIAL_SIZE_MB,
TARGET_SIZE/1024/1024 TARGET_SIZE_MB, FINAL_SIZE/1024/1024 FINAL_SIZE_MB
from V$MEMORY_RESIZE_OPS
order by START_TIME, END_TIME ;
START_TIM END_TIME STATUS COMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE_MB TARGET_SIZE_MB FINAL_SIZE_MB
--------- --------- --------- ------------------------- ------------- --------- ---------------------- --------------- -------------- -------------
17-DEC-15 17-DEC-15 COMPLETE shared pool STATIC shared_pool_size 0 148 148
17-DEC-15 17-DEC-15 COMPLETE PGA Target STATIC pga_aggregate_target 0 136 136
17-DEC-15 17-DEC-15 COMPLETE java pool STATIC java_pool_size 0 4 4
17-DEC-15 17-DEC-15 COMPLETE streams pool STATIC streams_pool_size 0 4 4
17-DEC-15 17-DEC-15 COMPLETE SGA Target STATIC sga_target 0 260 260
17-DEC-15 17-DEC-15 COMPLETE large pool STATIC large_pool_size 0 4 4
17-DEC-15 17-DEC-15 COMPLETE ASM Buffer Cache STATIC db_cache_size 0 0 0
17-DEC-15 17-DEC-15 COMPLETE DEFAULT buffer cache STATIC db_cache_size 0 92 92
17-DEC-15 17-DEC-15 COMPLETE DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0
17-DEC-15 17-DEC-15 COMPLETE DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0
17-DEC-15 17-DEC-15 COMPLETE DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0
START_TIM END_TIME STATUS COMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE_MB TARGET_SIZE_MB FINAL_SIZE_MB
--------- --------- --------- ------------------------- ------------- --------- ---------------------- --------------- -------------- -------------
17-DEC-15 17-DEC-15 COMPLETE DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0
17-DEC-15 17-DEC-15 COMPLETE DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0
17-DEC-15 17-DEC-15 COMPLETE KEEP buffer cache STATIC db_keep_cache_size 0 0 0
17-DEC-15 17-DEC-15 COMPLETE RECYCLE buffer cache STATIC db_recycle_cache_size 0 0 0
17-DEC-15 17-DEC-15 COMPLETE DEFAULT buffer cache INITIALIZING db_cache_size 92 92 92
16 rows selected.
SQL> select value from v$parameter where upper(name)='MEMORY_TARGET';
VALUE
--------------------------------------------------------------------------------
415236096
SQL> select value/1024/1024 mb
2 from v$parameter where upper(name) = 'SGA_MAX_SIZE';
MB
----------
396
-- size taken by a memory component
SQL> select pool, round(sum(BYTES)/1024/1024) MB
2 from V$SGASTAT
3 group by pool;
POOL MB
------------ ----------
98
java pool 4
streams pool 4
shared pool 148
large pool 4
SQL> select name , value/1024/1024 MB
2 from v$parameter
3 where upper(name) in (
4 'DB_CACHE_SIZE','DB_RECYCLE_CACHE_SIZE','DB_KEEP_CACHE_SIZE', '
5 DB_2k_CACHE_SIZE', 'DB_8k_CACHE_SIZE', 'DB_16k_CACHE_SIZE');
NAME MB
------------------------------ ----------
db_cache_size 0
db_keep_cache_size 0
db_recycle_cache_size 0
--For 10g
SQL> SELECT COMPONENT , CURRENT_SIZE/1024/1024 MB
2 FROM V$SGA_DYNAMIC_COMPONENTS
3 WHERE CURRENT_SIZE <>0;
COMPONENT MB
---------------------------------------------------------------- ----------
shared pool 148
large pool 4
java pool 4
streams pool 4
DEFAULT buffer cache 92
-- Oracle 11g
SQL> SELECT COMPONENT, ROUND(CURRENT_SIZE/1024/1024) CURRENT_SIZE ,
2 ROUND(MIN_SIZE/1024/1024) MIN, ROUND(MAX_SIZE/1024/1024) MAX
3 FROM V$MEMORY_DYNAMIC_COMPONENTS;
COMPONENT CURRENT_SIZE MIN MAX
---------------------------------------------------------------- ------------ ---------- ----------
shared pool 148 148 148
large pool 4 4 4
java pool 4 4 4
streams pool 4 4 4
SGA Target 260 260 260
DEFAULT buffer cache 92 92 92
KEEP buffer cache 0 0 0
RECYCLE buffer cache 0 0 0
DEFAULT 2K buffer cache 0 0 0
DEFAULT 4K buffer cache 0 0 0
DEFAULT 8K buffer cache 0 0 0
COMPONENT CURRENT_SIZE MIN MAX
---------------------------------------------------------------- ------------ ---------- ----------
DEFAULT 16K buffer cache 0 0 0
DEFAULT 32K buffer cache 0 0 0
Shared IO Pool 0 0 0
PGA Target 136 136 136
ASM Buffer Cache 0 0 0
-- To findout how Oracle has modified the memory area sizes by time
SQL> select START_TIME, END_TIME, STATUS, COMPONENT, OPER_TYPE, OPER_MODE,
PARAMETER, INITIAL_SIZE/1024/1024 INITIAL_SIZE_MB,
TARGET_SIZE/1024/1024 TARGET_SIZE_MB, FINAL_SIZE/1024/1024 FINAL_SIZE_MB
from V$MEMORY_RESIZE_OPS
order by START_TIME, END_TIME ;
START_TIM END_TIME STATUS COMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE_MB TARGET_SIZE_MB FINAL_SIZE_MB
--------- --------- --------- ------------------------- ------------- --------- ---------------------- --------------- -------------- -------------
17-DEC-15 17-DEC-15 COMPLETE shared pool STATIC shared_pool_size 0 148 148
17-DEC-15 17-DEC-15 COMPLETE PGA Target STATIC pga_aggregate_target 0 136 136
17-DEC-15 17-DEC-15 COMPLETE java pool STATIC java_pool_size 0 4 4
17-DEC-15 17-DEC-15 COMPLETE streams pool STATIC streams_pool_size 0 4 4
17-DEC-15 17-DEC-15 COMPLETE SGA Target STATIC sga_target 0 260 260
17-DEC-15 17-DEC-15 COMPLETE large pool STATIC large_pool_size 0 4 4
17-DEC-15 17-DEC-15 COMPLETE ASM Buffer Cache STATIC db_cache_size 0 0 0
17-DEC-15 17-DEC-15 COMPLETE DEFAULT buffer cache STATIC db_cache_size 0 92 92
17-DEC-15 17-DEC-15 COMPLETE DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0
17-DEC-15 17-DEC-15 COMPLETE DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0
17-DEC-15 17-DEC-15 COMPLETE DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0
START_TIM END_TIME STATUS COMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE_MB TARGET_SIZE_MB FINAL_SIZE_MB
--------- --------- --------- ------------------------- ------------- --------- ---------------------- --------------- -------------- -------------
17-DEC-15 17-DEC-15 COMPLETE DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0
17-DEC-15 17-DEC-15 COMPLETE DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0
17-DEC-15 17-DEC-15 COMPLETE KEEP buffer cache STATIC db_keep_cache_size 0 0 0
17-DEC-15 17-DEC-15 COMPLETE RECYCLE buffer cache STATIC db_recycle_cache_size 0 0 0
17-DEC-15 17-DEC-15 COMPLETE DEFAULT buffer cache INITIALIZING db_cache_size 92 92 92
16 rows selected.
