报错描述
在rac环境下创建索引,加上了并行 parallel 4。然后报错:
ORA-12801: 并行查询服务器 P005, instance racnode1:njcenter1 (1) 中发出错误信号
ORA-00600: 内部错误代码, 参数: [smboPut:fixedlen2], [3], [7], [0], [1891867], [], [], [], [], [], [], []
原因
检查rac各个节点状态都正常,在racnode1节点上不加parallel执行还是报错。怀疑是什么设置的问题。
Google了一下 ORA-600: [smboPut:fixedlen2]
Bug 7905006: ORA-600 [SMBOPUT:FIXEDLEN2], [2], [7], [0], USING ORDER BY WITH A DATE COLUMN
Status : Closed Not a Bug
The first two bytes of the DATE specifies the year and the rest is month and day offsets.
The old sort does not require a fixed-length value for DATE (probably using default values of the offsets) while the new sort does require a
fixed-length DATE value and enforces the check.
As DATE should always be 7 bytes, the new sort is more strict on this checking, which is preferable in some sense
描述这个问题是因为日期排序,看了下我建立的索引里确实有一个to_char(date,‘yyyy-mm-dd’)的函数。
解决办法
SOLUTION
- Set this parameter at the session level:
SQL> alter session set “_newsort_enabled” = false;
SQL> alter system flush shared_pool;
SQL> execute dbms_stats.gather_database_stats_job_proc;Or
Run the failing SQL reported in the trace file if this is not related to DBMS_STATS.
- Suggested action Plan for permanent fix :
You can set the above parameter in init.ora file or using ALTER SYSTEM command :
SQL> alter system set “_newsort_enabled” = false scope=spfile;
- If this is related to a specific table or row the data should be manually checked to confirm it is valid.
根据文档,先检查一下参数:_newsort_enabled=True
SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND ksppinm = '_newsort_enabled';
修改参数:
alter session set "_newsort_enabled" = false;
alter system set "_newsort_enabled" = false scope=spfile;
再执行就不报错了。
文档中:
SQL> alter system flush shared_pool;
SQL> execute dbms_stats.gather_database_stats_job_proc;
就没有继续执行了。