在Windows 平台遇上ORA-29339 错误

Kevin Zou

2011-8-31

在一个windows 2008， oracle 11.2.0.1平台上创建一个blocksize 为4K的表空间；

SQL> select * from v$version;

BANNER
------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create tablespace tt datafile 'L:\ORADATA\TEST\DATA11\tt01.dbf' size 10M bl
ocksize  4k;
create tablespace tt datafile 'L:\ORADATA\TEST\DATA11\tt01.dbf' size 10M blocksi
ze  4k
*
ERROR at line 1:
ORA-29339: tablespace block size 4096 does not match configured block sizes


SQL> alter system set db_4k_cache_size=10M;

System altered.

SQL> create tablespace tt datafile 'L:\ORADATA\TEST\DATA11\tt01.dbf' size 10M bl
ocksize  4k;

Tablespace created.

这是Oracle9i Database Administrator's Guide 中的一段话，解释ORA-29339错误的原因：

In order for the BLOCKSIZE clause to succeed, you must have the DB_CACHE_SIZE and at least one DB_nK_CACHE_SIZE initialization parameter set, and the integer you specify in this clause must correspond with the setting of one DB_nK_CACHE_SIZE parameter setting. Although redundant, specifying a BLOCKSIZE equal to the standard block size, as specified by the DB_BLOCK_SIZE initialization parameter, is allowed.

看到在windows 平台只要设定了指定的blocksize的buffer cache 就可以创建对应的blocksize的 tablespace

但在windwow平台不包括32K的Blocksize。如果要指定db_32k_cache_size 大小时会报ORA-00382的错误。

SQL> alter system set db_32k_cache_size=10M;

alter system set db_32k_cache_size=10M

*

ERROR at line 1:

ORA-32017: failure in updating SPFILE

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

下面是不同平台可以创建不同blocksize tablespace的列表：

AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel,

Sun SPARC Solaris:

DB_BLOCK_SIZE: 2048 to 16384 (Linux, Solaris)

2048 to 32768 (AIX, HP, Tru64)

Database Administrator's Guide for Windows:

DB_BLOCK_SIZE: 2048 to 16384 (Windows NT, 95, 98, 2000)

-THE END-