ORA-29339

I discovered a new error today. I was using Data Pump to copy a database from Sun SPARC to X86 and from Oracle 11.1.0.6.0 to 11.2.0.4.0 for testing purposes. I extracted the tablespace creation SQL from the old database like this. (This is only some of it.):

 

SQL> set lines 32000

SQL> set pages 50

SQL> exec dbms_metadata.set_transform_param(-

> DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);

 

PL/SQL procedure successfully completed.

 

SQL> select dbms_metadata.get_ddl(-

> 'TABLESPACE','PINDAR_LARGE_32KBLOCKS')

  2  from dual

  3  /

 

DBMS_METADATA.GET_DDL('TABLESPACE','PINDAR_LARGE_32KBLOCKS')

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

 

  CREATE TABLESPACE "PINDAR_LARGE_32KBLOCKS" DATAFILE

  '/cisdpt/nbadpt1/nba_data/pindar_large_32kblocks_a.dbf' SIZE 8388608000,

  '/cisdpt/nbadpt1/nba_data/pindar_large_32kblocks_b.dbf' SIZE 10485760000,

  '/cisdpt/nbadpt1/nba_data/pindar_large_32k_blocks_c.dbf' SIZE 15728640000

  LOGGING ONLINE PERMANENT BLOCKSIZE 32768

  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 209715200 SEGMENT SPACE MANAGEMENT AUTO;

 

SQL>

 

I did a global replace to change part of the file name to bring it in line with the file system naming on the X86 server. Then I ran the SQL to create the tablespace in the new database but it failed:

 

SQL>   CREATE TABLESPACE "PINDAR_LARGE_32KBLOCKS" DATAFILE

  2    '/database/GBNBAPF1/nba_data/pindar_large_32kblocks_a.dbf' SIZE 8388608000,

  3    '/database/GBNBAPF1/nba_data/pindar_large_32kblocks_b.dbf' SIZE 10485760000,

  4    '/database/GBNBAPF1/nba_data/pindar_large_32k_blocks_c.dbf' SIZE 15728640000

  5    LOGGING ONLINE PERMANENT BLOCKSIZE 32768

  6    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 209715200 SEGMENT SPACE MANAGEMENT AUTO;

  CREATE TABLESPACE "PINDAR_LARGE_32KBLOCKS" DATAFILE

*

ERROR at line 1:

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

 

SQL>

 

The db_block_size was 8192 in both the old and new databases but the old one had the following extra line in its parameter file:

 

db_32k_cache_size=12m

 

This is to set up a cache for tablespaces with a 32K block size. I added this line to the parameter file for the new database and bounced it. Then I was able to create the tablespace there successfully:

 

SQL> CREATE TABLESPACE "PINDAR_LARGE_32KBLOCKS" DATAFILE

  2  '/database/GBNBAPF1/nba_data/pindar_large_32kblocks_a.dbf' SIZE 8388608000,

  3  '/database/GBNBAPF1/nba_data/pindar_large_32kblocks_b.dbf' SIZE 10485760000,

  4  '/database/GBNBAPF1/nba_data/pindar_large_32k_blocks_c.dbf' SIZE 15728640000

  5  LOGGING ONLINE PERMANENT BLOCKSIZE 32768

  6  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 209715200 SEGMENT SPACE MANAGEMENT AUTO;

 

Tablespace created.

 

SQL>