結論

1,v$sga_resize_ops記錄已完成的近期850次SGA元件重定義大小的操作，注意是已經完成
與之對應的是v$sga_current_resize_ops它僅記錄當前正在操作中的SGA元件重定義操作，注意是正在操作中的
2,由此引申出，ORACLE任何知識皆有正反性，對應即有對應的知識點，所以考慮問題要從自反和辯證方面分析，才會更進一步理解ORACLE
3,不要基於__db_cache_size(以及類似的SGA其它元件)顯式調整SGA對應元件的大小，它不會記錄到v$sga_resize_ops
4,可以隨意增減__db_cache_size(其它類似),ORACLE不會有任何報錯
5,如果僅是單方面調整了db_cache_size或其它元件大小（非隱含對應引數），不管成功或失敗，皆會記錄到v$sga_resize_ops
6,如果要增加一個SGA元件大小，先要減少其它SGA元件的大小，然後才能增加這個SGA元件的大小，所以這是2個連續的操作
7,並且最終要調整的SGA元件大小並非和要調整的大小一致，我想這取決於SGA元件的大小以及各個元件內部的儲存結構以入要調整元件的大小

測試

SQL> select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> col oper_type for a15
SQL> col parameter for a20
SQL> col component for a30
SQL> set linesize 300

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> col oper_mode noprint

----未調整前的SGA元件重定義大小操作資訊，此檢視記錄已經近期完成的850次重定義操作記錄，如果是當前正在重定義操作，請到v$sga_current_resize_ops檢視，二檢視結構相同
SQL> select component,oper_type,oper_mode,parameter,initial_size,target_size/1024/1024 as target_size,final_size/1024/1024 as final_size,status,start_time,end_time from v$sga_resize_ops;

COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
streams pool STATIC streams_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
ASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT buffer cache STATIC db_cache_size 0 48 48 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
KEEP buffer cache STATIC db_keep_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
RECYCLE buffer cache STATIC db_recycle_cache_siz 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
e

DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool STATIC shared_pool_size 0 464 464 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
java pool STATIC java_pool_size 0 24 24 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
large pool STATIC large_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

13 rows selected.

SQL> show parameter sga_

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
sga_max_size big integer 548M
sga_target big integer 0
SQL> show parameter _pool

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
buffer_pool_keep string
buffer_pool_recycle string
global_context_pool_size string
java_pool_size big integer 24M
large_pool_size big integer 0
olap_page_pool_size big integer 0
shared_pool_reserved_size big integer 24326963
shared_pool_size big integer 464M
streams_pool_size big integer 0

NAME_1 VALUE_1 DESC1
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
__db_cache_size 50331648 Actual size of DEFAULT buffer pool for standard bl
ock size buffers

__shared_pool_size 486539264 Actual size in bytes of shared pool

SQL> select 50331648/1024/1024 as db_cache_size,486539264/1024/1024 as shared_pool_size from dual;

DB_CACHE_SIZE SHARED_POOL_SIZE
------------- ----------------
48 464

---我基於v$sga_resize_ops之前的測試，對於其列final_size，發現其與此__db_cache_size及__shared_pool_size,即和各個元件當前實際分配大小有關，所以我想看看直接手工調整共享池及DB CACHE大小，看不會
記錄到上述檢視中
SQL> alter system set "__shared_pool_size"=400m;

System altered.

SQL> alter system set "__db_cache_size"=112m;

System altered.

---可見雖然顯式調整了共享池以及DB CACHE的當前實際大小，sga仍未發生重定義大小操作
SQL> select component,oper_type,oper_mode,parameter,initial_size,target_size/1024/1024 as target_size,final_size/1024/1024 as final_size,status,start_time,end_time from v$sga_resize_ops;

COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
streams pool STATIC streams_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
ASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT buffer cache STATIC db_cache_size 0 48 48 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
KEEP buffer cache STATIC db_keep_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
RECYCLE buffer cache STATIC db_recycle_cache_siz 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
e

DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool STATIC shared_pool_size 0 464 464 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
java pool STATIC java_pool_size 0 24 24 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
large pool STATIC large_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

13 rows selected.

---可見db cache實際大小沒有調整，而共享池大小有調整
NAME_1 VALUE_1 DESC1
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
__db_cache_size 50331648 Actual size of DEFAULT buffer pool for standard bl
ock size buffers

__shared_pool_size 419430400 Actual size in bytes of shared pool

SQL> alter system set "__shared_pool_size"=50m;

System altered.

---發現db cache可以隨意調整，即使調錯也不報錯
SQL> alter system set "__db_cache_size"=100g;

System altered.

SQL> show parameter db_cache

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 48M
SQL> show parameter shared_pool

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
shared_pool_reserved_size big integer 24326963
shared_pool_size big integer 464M

----可見如果把某個SGA元件調整過小，則會記錄SGA元件重定義操作
SQL> alter system set shared_pool_size=10m;
alter system set shared_pool_size=10m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04034: unable to shrink pool to specified size

--可見target_size為顯式要調整的值，final_size為基於SGA各元件當前實際情況最終要調整的值
SQL> select component,oper_type,oper_mode,parameter,initial_size,target_size/1024/1024 as target_size,final_size/1024/1024 as final_size,status,start_time,end_time from v$sga_resize_ops;

COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool STATIC shared_pool_size 0 464 464 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
streams pool STATIC streams_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
ASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT buffer cache STATIC db_cache_size 0 48 48 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
RECYCLE buffer cache STATIC db_recycle_cache_siz 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
e

KEEP buffer cache STATIC db_keep_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
java pool STATIC java_pool_size 0 24 24 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
large pool STATIC large_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool SHRINK shared_pool_size 486539264 12 268 ERROR 2015-12-15 06:57:38 2015-12-15 06:57:40 ---上述調整共享池操作

14 rows selected.

SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
shared_pool_size big integer 268M

NAME_1 VALUE_1 DESC1
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
__shared_pool_size 281018368 Actual size in bytes of shared pool

SQL> select 281018368/1024/1024 mb from dual;

MB
----------
268

---增加DB CACHE的大小操作
SQL> alter system set db_cache_size=1g;
alter system set db_cache_size=1g
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache

SQL> select component,oper_type,oper_mode,parameter,initial_size,target_size/1024/1024 as target_size,final_size/1024/1024 as final_size,status,start_time,end_time from v$sga_resize_ops;

COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
streams pool STATIC streams_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
ASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT buffer cache STATIC db_cache_size 0 48 48 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
KEEP buffer cache STATIC db_keep_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
RECYCLE buffer cache STATIC db_recycle_cache_siz 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
e

COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool STATIC shared_pool_size 0 464 464 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
java pool STATIC java_pool_size 0 24 24 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
large pool STATIC large_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool SHRINK shared_pool_size 486539264 12 268 ERROR 2015-12-15 06:57:38 2015-12-15 06:57:40
DEFAULT buffer cache GROW db_cache_size 50331648 1024 244 ERROR 2015-12-15 07:05:05 2015-12-15 07:05:05 ---當前增加DB CACHE的操作

15 rows selected.

SQL> show parameter db_cache

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 244M
SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
shared_pool_size big integer 268M

SQL> alter system set db_cache_size=100m;

System altered.

SQL> alter system set shared_pool_size=412m;

System altered.

SQL> show parameter db_cache

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 100M
SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
shared_pool_size big integer 412M

SQL> select component,oper_type,oper_mode,parameter,initial_size,target_size/1024/1024 as target_size,final_size/1024/1024 as final_size,status,start_time,end_time from v$sga_resize_ops;

COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
streams pool STATIC streams_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
large pool STATIC large_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
java pool STATIC java_pool_size 0 24 24 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
ASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT buffer cache STATIC db_cache_size 0 48 48 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
KEEP buffer cache STATIC db_keep_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
RECYCLE buffer cache STATIC db_recycle_cache_siz 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
e

DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool STATIC shared_pool_size 0 464 464 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool SHRINK shared_pool_size 486539264 12 268 ERROR 2015-12-15 06:57:38 2015-12-15 06:57:40
DEFAULT buffer cache GROW db_cache_size 50331648 1024 244 ERROR 2015-12-15 07:05:05 2015-12-15 07:05:05
DEFAULT buffer cache SHRINK db_cache_size 255852544 100 100 COMPLETE 2015-12-15 07:12:29 2015-12-15 07:12:29 ---上述減少db cache
shared pool GROW shared_pool_size 281018368 412 412 COMPLETE 2015-12-15 07:13:03 2015-12-15 07:13:03 ---上述增加共享池

17 rows selected.

SQL> show parameter db_cache

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 100M
SQL> show parameter shared_pool

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
shared_pool_reserved_size big integer 24326963
shared_pool_size big integer 412M
SQL> alter system set shared_pool_size=300m;

System altered.

SQL> alter system set db_cache_size=242m;
alter system set db_cache_size=242m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache

SQL> alter system set db_cache_size=212m;

System altered.

SQL> select component,oper_type,oper_mode,parameter,initial_size,target_size/1024/1024 as target_size,final_size/1024/1024 as final_size,status,start_time,end_time from v$sga_resize_ops;

COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
RECYCLE buffer cache STATIC db_recycle_cache_siz 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
e

streams pool STATIC streams_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT buffer cache STATIC db_cache_size 0 48 48 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
KEEP buffer cache STATIC db_keep_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
ASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
large pool STATIC large_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
java pool STATIC java_pool_size 0 24 24 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool STATIC shared_pool_size 0 464 464 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool SHRINK shared_pool_size 486539264 12 268 ERROR 2015-12-15 06:57:38 2015-12-15 06:57:40
DEFAULT buffer cache GROW db_cache_size 50331648 1024 244 ERROR 2015-12-15 07:05:05 2015-12-15 07:05:05
DEFAULT buffer cache SHRINK db_cache_size 255852544 100 100 COMPLETE 2015-12-15 07:12:29 2015-12-15 07:12:29
shared pool GROW shared_pool_size 281018368 412 412 COMPLETE 2015-12-15 07:13:03 2015-12-15 07:13:03
shared pool SHRINK shared_pool_size 432013312 300 300 COMPLETE 2015-12-15 07:16:28 2015-12-15 07:16:28
DEFAULT buffer cache GROW db_cache_size 104857600 244 212 ERROR 2015-12-15 07:16:42 2015-12-15 07:16:42

19 rows selected.

SQL> alter system set db_cache_size=170m;

System altered.

SQL> select component,oper_type,oper_mode,parameter,initial_size,target_size/1024/1024 as target_size,final_size/1024/1024 as final_size,status,start_time,end_time from v$sga_resize_ops;

COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
RECYCLE buffer cache STATIC db_recycle_cache_siz 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
e

streams pool STATIC streams_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT buffer cache STATIC db_cache_size 0 48 48 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
KEEP buffer cache STATIC db_keep_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
ASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
large pool STATIC large_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
java pool STATIC java_pool_size 0 24 24 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool STATIC shared_pool_size 0 464 464 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool SHRINK shared_pool_size 486539264 12 268 ERROR 2015-12-15 06:57:38 2015-12-15 06:57:40
DEFAULT buffer cache GROW db_cache_size 50331648 1024 244 ERROR 2015-12-15 07:05:05 2015-12-15 07:05:05
DEFAULT buffer cache SHRINK db_cache_size 255852544 100 100 COMPLETE 2015-12-15 07:12:29 2015-12-15 07:12:29
shared pool GROW shared_pool_size 281018368 412 412 COMPLETE 2015-12-15 07:13:03 2015-12-15 07:13:03
shared pool SHRINK shared_pool_size 432013312 300 300 COMPLETE 2015-12-15 07:16:28 2015-12-15 07:16:28
DEFAULT buffer cache GROW db_cache_size 104857600 244 212 ERROR 2015-12-15 07:16:42 2015-12-15 07:16:42
DEFAULT buffer cache SHRINK db_cache_size 222298112 172 172 COMPLETE 2015-12-15 07:39:31 2015-12-15 07:39:31

20 rows selected.

SQL>

進一步理解oracle11g之v$sga_resize_ops

結論

測試

相關文章