Tuesday, November 1, 2016

ORA-29339: tablespace block size 16384 does not match configured block sizes

While I create non standart db_block_size tablespace I faced ORA-29339: tablespace block size 16384 does not match configured block sizes.

16k = 16*1024 = 16384

 SQL> CREATE TABLESPACE DATA DATAFILE  
  '/u01/app/oracle/oradata/cdb1/TEST/data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 31G  
 LOGGING  
 DEFAULT  
  NO INMEMORY  
 EXTENT MANAGEMENT LOCAL AUTOALLOCATE  
 BLOCKSIZE 16K  
 SEGMENT SPACE MANAGEMENT AUTO  
 FLASHBACK ON;  

ORA-29339

 SQL> select name, block_size, current_size from v$buffer_pool;  
 NAME                 BLOCK_SIZE CURRENT_SIZE  
 -------------------- ---------- ------------  
 DEFAULT              8192       132096  

Check db_*k_cache_size parameters

SQL> show parameter cache_size  
 NAME     TYPE VALUE  
 ------------------------------------ ----------- ------------------------------  
 client_result_cache_size   big integer 0  
 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_flash_cache_size    big integer 0  
 db_keep_cache_size    big integer 0  
 db_recycle_cache_size    big integer 0  
 SQL>  

Also db_block_size

 SQL> show parameter db_block_size  
 NAME     TYPE VALUE  
 ------------------------------------ ----------- ------------------------------  
 db_block_size    integer 8192  
 SQL>  


I need to set db_16k_cache_size.
 SQL> alter system set db_16k_cache_size = 128M scope=both; 


Then try again


 SQL> CREATE TABLESPACE DATA DATAFILE  
  '/u01/app/oracle/oradata/cdb1/TEST/data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 31G  
 LOGGING  
 DEFAULT  
  NO INMEMORY  
 EXTENT MANAGEMENT LOCAL AUTOALLOCATE  
 BLOCKSIZE 16K  
 SEGMENT SPACE MANAGEMENT AUTO  
 FLASHBACK ON;  
 Tablespace created.  

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...