Zahid Karim
Recent Posts
1.Draw plus and cross sign into PDF via PLSQL using PL_FPDF
2.Draw Triangle directly into PDF via PLSQL using PL_FPDF
3.Write fast and efficient PLSQL - DBMS_PROFILER
4.Fix physically corrupt data blocks using PLSQL - DBMS_REPAIR
5.Encrypt or Decrypt sensitive data using PLSQL - DBMS_CRYPTO
Find me on
Handling Nulls in a Primary and Foreign key relationship How to install ASM instance on a non-asm Oracle database

Oracle 10g db_nk_cache_size Parameters

Posted by Zahid on November 18, 2009.

These initialization parameters are used to configure buffer caches with a different size from the database default block size (defined by db_block_size parameter). By default there is always one buffer cache configured in the database which is called Default Buffer Cache and its blocks are always of size equal to the db_block_size. The nk_caches are not set by default and you have to configure them when you need them.

The question arises WHY? Why would you need to have a cache with different block size in the database?

One example of this could be where you want to create a tablespace with 16k block size in a 8k block size database because after you create this tablespace and do something with it, you want to then transport it to another database whose default block size is 16k and there this tablespace has to reside forever.

Lets see how to create a tablespace like this and how to configure nk_cache_size for it.

SQL> show parameter db_block_size NAME TYPE VALUE ------------------- ----------- -------------- db_block_size integer 8192 SQL> SQL> show parameter k_cache 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 SQL> SQL> create tablespace ts_16k 2 datafile '/d01/apps/oradata/oraxpo/ts_16l01.dbf' 3 size 10m autoextend on maxsize 20m 4 extent management local 5 segment space management auto 6 blocksize 16k; create tablespace ts_16k * ERROR at line 1: ORA-29339: tablespace block size 16384 does not match configured block sizes /* Can't have a block size 16 tablespace unless there is a 16k buffer cache configured. */ SQL> alter system set db_16k_cache_size=20m scope=both; System altered. SQL> show parameter k_cache NAME TYPE VALUE ------------------- ----------- ----------- db_16k_cache_size big integer 20M 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 SQL> SQL> create tablespace ts_16k 2 datafile '/d01/apps/oradata/oraxpo/ts_16l01.dbf' 3 size 10m autoextend on maxsize 20m 4 extent management local 5 segment space management auto 6 blocksize 16k; Tablespace created. SQL> alter user scott quota unlimited on ts_16k; User altered. SQL> conn scott/tiger Connected. SQL> create table ts_16k_table tablespace ts_16k 2 as 3 select * from all_objects; Table created. SQL> create table ts_8k_table tablespace users 2 as 3 select * from all_objects; Table created. SQL> analyze table ts_8k_table compute statistics; Table analyzed. SQL> analyze table ts_16k_table compute statistics; Table analyzed. SQL> select tablespace_name , table_name , blocks , num_rows 2 from user_tables 3 where table_name like 'TS%'; TABLESPACE_NAME TABLE_NAME BLOCKS NUM_ROWS ------------------ --------------- ---------- ---------- TS_16K TS_16K_TABLE 291 40694 USERS TS_8K_TABLE 586 40695 SQL>

Both tables have almost same number of rows. The table "ts_8k_table" is using 586 blocks and the table "ts_16k_table" using 291 which is half of the 8k table.

See also:
How to transport a tablespace in Oracle 10g