热衷技术，热爱交流
分类： Oracle
2013-11-15 00:04:43
今天遇到如下问题：
sys@HX> SELECT distinct e.owner,e.segment_name,e.segment_type
2 FROM dba_extents e,
(select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc) where rownum<=10) b
WHERE e.relative_fno=b.dbarfil
5 AND e.block_id<=b.dbablk
6 AND e.block_id+e.blocks>b.dbablk;
FROM dba_extents e,
*
ERROR at line 2:
ORA-00379: 缓冲池 DEFAULT 中无法提供 32K 块大小的空闲缓冲区
猜想是因为之前设置了db_32k_cache_size参数，并且建立了32k block_size大小的表空间，但是现在用了旧的参数文件，导致无法为相应的块按照32k的block_size来分配buffer cache,
sys@HX> show parameter cache;
NAME |TYPE |VALUE
------------------------------------|-----------|------------------------------
client_result_cache_lag |big integer|3000
client_result_cache_size |big integer|0
db_16k_cache_size |big integer|0
db_2k_cache_size |big integer|0
db_32k_cache_size |big integer|0
db_4k_cache_size |big integer|0
db_8k_cache_size |big integer|0
db_cache_advice |string |ON
sys@HX> select tablespace_name,block_size from dba_tablespaces;
TABLESPACE_NAME |BLOCK_SIZE
--------------------|----------
SYSTEM | 8192
SYSAUX | 8192
UNDOTBS1 | 8192
USERS | 8192
TEMPGROUP11 | 8192
HR | 8192
KKKK | 32768
CLUSTER1 | 8192
8 rows selected.
修改参数，或者删除kkkk表空间即可
sys@HX> alter system set db_32k_cache_size=16m;
System altered.
sys@HX> show parameter cache;
NAME |TYPE |VALUE
------------------------------------|-----------|------------------------------
client_result_cache_lag |big integer|3000
client_result_cache_size |big integer|0
db_16k_cache_size |big integer|0
db_2k_cache_size |big integer|0
db_32k_cache_size |big integer|16M
db_4k_cache_size |big integer|0
db_8k_cache_size |big integer|0
sys@HX> select tablespace_name,block_size from dba_tablespaces;
TABLESPACE_NAME |BLOCK_SIZE
--------------------|----------
SYSTEM | 8192
SYSAUX | 8192
UNDOTBS1 | 8192
USERS | 8192
TEMPGROUP11 | 8192
HR | 8192
KKKK | 32768
CLUSTER1 | 8192
8 rows selected.
问题解决。
