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

Issue:

Unable to create a Tablespace with larger block size.

Error:

Cause:

The block size of the tablespace to be created does not match the block sizes configured in the database.

Note:

Currently database running with 8K block size, we are trying to create tablespace with 16K block size.

Solution:

Configure the appropriate cache for the block size of this tablespace using below parameter.

db_2k_cache_size

db_4k_cache_size

db_8k_cache_size

db_16k_cache_size

db_32K_cache_size

Logs:

[root@oraclelab1 ~]# su - oracle

Last login: Tue Jul 25 08:06:22 IST 2023 on pts/0

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ . oraenv

ORACLE_SID = [oracle] ? DEVDB

The Oracle base has been set to /u01/app/oracle

[oracle@oraclelab1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 25 18:20:28 2023

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> show parameter db_block_size

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_block_size integer 8192

SQL> select NAME from v$tablespace;

NAME

------------------------------

SYSAUX

SYSTEM

UNDOTBS1

USERS

TEMP

TEST2

TEST4

7 rows selected.

SQL> select NAME from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_lcx8tlpt_.dbf

/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_lcx8votl_.dbf

/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_lcx8wgxt_.dbf

/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_lcx8wj14_.dbf

/u01/app/oracle/oradata/DEVDB/datafile/test2.dbf

/u01/app/oracle/oradata/DEVDB/datafile/test4.dbf

6 rows selected.

SQL> set pages 100 lines 1000

SQL> set pages 1000 lines 1000

SQL> col tablespace_name format a16;

col file_name format a50;

SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024

FROM DBA_DATA_FILES;

TABLESPACE_NAME FILE_NAME BYTES/1024/1024

---------------- -------------------------------------------------- ---------------

SYSTEM /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_syste 1020

m_lcx8tlpt_.dbf

SYSAUX /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysau 580

x_lcx8votl_.dbf

USERS /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users 5

_lcx8wj14_.dbf

UNDOTBS1 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undot 390

bs1_lcx8wgxt_.dbf

TEST2 /u01/app/oracle/oradata/DEVDB/datafile/test2.dbf 100

TEST4 /u01/app/oracle/oradata/DEVDB/datafile/test4.dbf 100

6 rows selected.

SQL> select NAME,BIGFILE from v$tablespace;

NAME BIG

------------------------------ ---

SYSAUX NO

SYSTEM NO

UNDOTBS1 NO

USERS NO

TEMP NO

TEST2 NO

TEST4 NO

7 rows selected.

SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024,AUTOEXTENSIBLE FROM DBA_DATA_FILES;

TABLESPACE_NAME FILE_NAME BYTES/1024/1024 AUT

---------------- -------------------------------------------------- --------------- ---

SYSTEM /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_syste 1020 YES

m_lcx8tlpt_.dbf

SYSAUX /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysau 580 YES

x_lcx8votl_.dbf

UNDOTBS1 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undot 390 YES

bs1_lcx8wgxt_.dbf

USERS /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users 5 YES

_lcx8wj14_.dbf

TEST2 /u01/app/oracle/oradata/DEVDB/datafile/test2.dbf 100 YES

TEST4 /u01/app/oracle/oradata/DEVDB/datafile/test4.dbf 100 NO

6 rows selected.

While creating Tablespace with 16k block size which failed

SQL> CREATE TABLESPACE test_16k_ts DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/test_16k_ts01.dbf' SIZE 10M BLOCKSIZE 16384;

CREATE TABLESPACE test_16k_ts DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/test_16k_ts01.dbf' SIZE 10M BLOCKSIZE 16384

ERROR at line 1:

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

Solution is to set 16k db_block size, for that I am setting db_16k_cache_size=112M

SQL> alter system set db_16k_cache_size=112M scope=spfile;

System altered.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 3690985856 bytes

Fixed Size 8903040 bytes

Variable Size 704643072 bytes

Database Buffers 2969567232 bytes

Redo Buffers 7872512 bytes

Database mounted.

Database opened.

SQL> show parameter db_16k_cache_size

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_16k_cache_size big integer 112M

SQL> CREATE TABLESPACE test_16k_ts DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/test_16k_ts01.dbf' SIZE 10M BLOCKSIZE 16384;

Tablespace created.

SQL>

SQL> select TABLESPACE_NAME,BLOCK_SIZE from dba_tablespaces;

TABLESPACE_NAME BLOCK_SIZE

---------------- ----------

SYSTEM 8192

SYSAUX 8192

UNDOTBS1 8192

TEMP 8192

USERS 8192

TEST2 8192

TEST4 8192

TEST_16K_TS 16384

8 rows selected.

SQL>

Extra note:

A. How many default tablespaces are available in Oracle Database?

B. Can database user be part of Multiple Tablespaces in Oracle?

C. Can database user creates objects (example: Tables) into Multiple Tablespaces?

1. create an user and and user will default get assigned to default tablespace of a database (USERS)

SQL> create user mallik identified by mallik;

User created.

SQL> grant dba to mallik;

Grant succeeded.

SQL> select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where USERNAME='MALLIK';

USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

------------ -------------------------------- ------------------------------ ------------------------------

MALLIK OPEN USERS TEMP

SQL>

2. Can user creates table or ojects into multiple tablespaces?

Answer: Yes

create table EMP (SLNO number, NAME varchar2(15));

create table EMP_16K (SLNO number, NAME varchar2(15)) tablespace test_16k_ts;

SQL> conn mallik/mallik

Connected.

SQL> show user

USER is "MALLIK"

SQL> create table EMP (SLNO number, NAME varchar2(15));

Table created.

SQL> create table EMP_16K (SLNO number, NAME varchar2(15)) tablespace test_16k_ts;

Table created.

SQL>

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLE_NAME like 'EMP%';

OWNER TABLE_NAME TABLESPACE_NAME

--------- -------------- -------------------

MALLIK EMP USERS

MALLIK EMP_16K TEST_16K_TS

SQL>

3. Can we move a object from one tablespace to another tablespace?

Answer: Yes

SQL> alter table MALLIK.EMP move tablespace TEST_16K_TS;

Table altered.

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLE_NAME like 'EMP%';

OWNER TABLE_NAME TABLESPACE_NAME

-------- ----------- ----------------------

MALLIK EMP_16K TEST_16K_TS

MALLIK EMP TEST_16K_TS

SQL>

Regards,

Mallik

Vismo Technologies — Oracle DBA Knowledge Hub

Monday, July 31, 2023