My oracle db block size is 32K but development team want to create tablespaces with multiple block size 8k,4k and 16k we have to configure db_cash_size parameter therefore:
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 32768
SQL> select component,current_size from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
shared pool 335544320
large pool 603979776
java pool 16777216
streams pool 16777216
DEFAULT buffer cache 352321536
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
DEFAULT 32K buffer cache 0
Shared IO Pool 0
ASM Buffer Cache
*Action: Configure the appropriate cache for the block size of this
tablespace using one of the various (db_2k_cache_size,
db_4k_cache_size, db_8k_cache_size, db_16k_cache_size,
db_32K_cache_size) parameters.
SQL>alter system set db_2k_cache_size=2m;
SQL>alter system set db_4k_cache_size=4m;
SQL>alter system set db_8k_cache_size=8m;
SQL>alter system set db_16k_cache_size=16m;
SQL> select component,current_size from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
shared pool 335544320
large pool 33554432
java pool 16777216
streams pool 16777216
DEFAULT buffer cache 922746880
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 16777216
DEFAULT 4K buffer cache 16777216
DEFAULT 8K buffer cache 16777216
DEFAULT 16K buffer cache 16777216
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
DEFAULT 32K buffer cache 0
Shared IO Pool 0
ASM Buffer Cache 0
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 32768
SQL> select component,current_size from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
shared pool 335544320
large pool 603979776
java pool 16777216
streams pool 16777216
DEFAULT buffer cache 352321536
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
DEFAULT 32K buffer cache 0
Shared IO Pool 0
ASM Buffer Cache
*Action: Configure the appropriate cache for the block size of this
tablespace using one of the various (db_2k_cache_size,
db_4k_cache_size, db_8k_cache_size, db_16k_cache_size,
db_32K_cache_size) parameters.
SQL>alter system set db_2k_cache_size=2m;
SQL>alter system set db_4k_cache_size=4m;
SQL>alter system set db_8k_cache_size=8m;
SQL>alter system set db_16k_cache_size=16m;
SQL> select component,current_size from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
shared pool 335544320
large pool 33554432
java pool 16777216
streams pool 16777216
DEFAULT buffer cache 922746880
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 16777216
DEFAULT 4K buffer cache 16777216
DEFAULT 8K buffer cache 16777216
DEFAULT 16K buffer cache 16777216
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
DEFAULT 32K buffer cache 0
Shared IO Pool 0
ASM Buffer Cache 0
Now you can create tablespaces with multiple db block size in oracle database.
No comments:
Post a Comment