众所周知,db_block_size在建库后并不能修改,但是我们面对一些特殊需求的时候,总是想把db_block_size调大一点或者调小一点。我们可以通过db_nk_cache_size去实现我们这些需求。
SQL> show parameter cache_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 4M
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SQL> alter system set db_16k_cache_size=1m;
System altered
SQL> show parameter cache_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 4M
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SQL> show parameter cpu;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 1
parallel_threads_per_cpu integer 2
SQL> select t.NAME,t.BYTES/1024/1024 as "size(M)" from v$sgainfo t where t.name='Granule Size';
NAME size(M)
-------------------------------- ----------
Granule Size 4
SQL> create tablespace cache_size datafile 'cache_size_001.dbf' size 16M BLOCKSIZE 16K;
Tablespace created.
SQL> select t1.FILE_NAME,t1.USER_BYTES/t1.USER_BLOCKS/1024 as "per_block_size(K)" from dba_data_files t1
where t1.TABLESPACE_NAME in (upper('users'),upper('cache_size'));
FILE_NAME per_block_size(K)
------------------------------------------------------------------ -----------------
/u01/app/oracle/oradata/xiaoyao/dfile/users01.dbf 8
/u01/app/oracle/product/10.2.0/dbhome_1/dbs/cache_size_001.dbf 16
SQL> create table tmp_test (id int,image clob) tablespace cache_size;
Table created
SQL> create table tmp_czx (id int,image clob);
Table created
SQL> select distinct(t.segment_name),
t.BYTES/t.BLOCKS/1024 as "per_block_size(K)"
from dba_extents t
where t.segment_name in ('TMP_TEST','TMP_CZX') and t.owner=upper('system');
SEGMENT_NAME per_block_size(K)
------------------ -----------------
TMP_TEST 16
TMP_CZX 8
SQL> alter system set db_16k_cache_size=0;
System altered
SQL> show parameter cache_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SQL> alter system set db_16k_cache_size=0;
System altered
insert into tmp_test(id) values(1);
SQL> insert into tmp_test(id) values(1);
insert into tmp_test(id) values(1)
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 16K
SQL> alter system set db_16k_cache_size=1m;
System altered
SQL> show parameter cache_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 4M
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SQL> insert into tmp_test(id) values(1);
1 row inserted
SQL> commit;
Commit complete
SQL> show parameter db_block
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TRUE
db_block_size integer 8192
SQL> alter system set db_8k_cache_size=1m;
alter system set db_8k_cache_size=1m
ORA-32017: failure in updating SPFILE
ORA-00380: cannot specify db_8k_cache_size since 8K is the standard block size
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 300M
sga_target big integer 300M
SQL> alter system set db_16k_cache_size=136m;
System altered
SQL> alter system set db_16k_cache_size=140m;
alter system set db_16k_cache_size=140m
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache
SQL> alter system set db_16k_cache_size=7m;
System altered
SQL> show parameter cache_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 8M
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
ps:
db_nk_cache_size其实就是设置一个处理一些特殊block size的表空间。如果这个值为0,那么就无法使用这些表空间了。