ORA-00382: 32768 not a valid block size, valid range [2048..16384]

 Scenario Preview: 

Recently we perform some row migration and row chaining activity  as we find out on window operating system we are unable to increase block size 32kb might be the reason due to page size in window.

Error: 

ORA-00382: 32768 not a valid block size, valid range [2048..16384] 

C:\Users\varun>sqlplus sys/system123@orcl as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 4 23:07:56 2021

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> ALTER SYSTEM SET DB_32K_CACHE_SIZE= 160M;

ALTER SYSTEM SET DB_32K_CACHE_SIZE= 160M

*

ERROR at line 1:

ORA-32017: failure in updating SPFILE

ORA-00382: 32768 not a valid block size, valid range [2048..16384]

SQL> CREATE TABLESPACE TBS16K111 DATAFILE 'C:\APP\VARUN\ORADATA\ORCL\PDB\tbs16k.dbf' SIZE 160M AUTOEXTEND ON MAXSIZE 1000M BLOCKSIZE 32K;

CREATE TABLESPACE TBS16K111 DATAFILE 'C:\APP\VARUN\ORADATA\ORCL\PDB\tbs16k.dbf' SIZE 160M AUTOEXTEND ON MAXSIZE 1000M BLOCKSIZE 32K

*

ERROR at line 1:

ORA-00382: 32768 not a valid block size, valid range [2048..16384]

SQL>

SQL>

SQL> ALTER SYSTEM SET DB_16K_CACHE_SIZE= 160M;

System altered.

SQL> SELECT NAME, BLOCK_SIZE, CURRENT_SIZE FROM V$BUFFER_POOL;

NAME                 BLOCK_SIZE CURRENT_SIZE

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

DEFAULT                    8192         3552

DEFAULT                   16384          160

SQL>

SQL> CREATE TABLESPACE TBS16K DATAFILE 'C:\APP\VARUN\ORADATA\ORCL\PDB\tbs16k.dbf' SIZE 160M AUTOEXTEND ON MAXSIZE 1000M BLOCKSIZE 16K;

Tablespace created.

SQL>

Solution Testing : 

• Set DB_16K_CACHE_SIZE scope is memory 

SQL> ALTER SYSTEM SET DB_16K_CACHE_SIZE= 160M;

System altered.

SQL> SELECT NAME, BLOCK_SIZE, CURRENT_SIZE FROM V$BUFFER_POOL;

NAME                 BLOCK_SIZE CURRENT_SIZE

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

DEFAULT                    8192         3552

DEFAULT                   16384          160

• Create Tablespace TBS16K with block size 16K

SQL> CREATE TABLESPACE TBS16K DATAFILE 'C:\APP\VARUN\ORADATA\ORCL\PDB\tbs16k.dbf' SIZE 160M AUTOEXTEND ON MAXSIZE 1000M BLOCKSIZE 16K;

Tablespace created.

• Set Quota to schema on tablespace

SQL> ALTER USER SOE QUOTA UNLIMITED ON TBS16K;

User altered.

SQL> conn soe/soe@pdb

Connected.

• Move table cust to new tablespace

SQL> ALTER TABLE CUST MOVE TABLESPACE tbs16k;

Table altered.