ORA-29339: tablespace block size string does not match configured block sizes
Issue:
Unable to create a Tablespace with larger block size.
Error:
ORA-29339: tablespace block size string does not match configured block sizes
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
Copyright (c) 1982, 2019, Oracle. All rights reserved.
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
No comments:
Post a Comment