Sometimes it’s really hard even to create reproducible test case to send it to oracle support, especially in case of intermittent errors.
In such cases, I think it would be really great to have access to similar service requests or bugs of other oracle clients.
So while my poll about knowledge sharing is still active, I want to share a couple of bugs we have faced after upgrade to 12.2 (and one bug from Eric van Roon). I’m going to remove the bugs from this list when they become “public” or “fixed”.
If you want to add own findings into this list, you can add them into comments. To make this process easier, you can provide just symptomps, short description and the link to own post with details – I’ll add it just as a link.
Symptomps | Description | Links |
---|---|---|
Intermittent ORA-01483 | After upgrade to 12.2 we started getting “ORA-01483: invalid length for DATE or NUMBER bind variable” in different applications using different oracle drivers. Interestingly, that after reconnect, Oracle processes the same statement with same bind variables successfully. Looking into errorstack dump (alter system set events ‘1483 trace name errorstack level 3, lifetime 5’;) we have found that oracle mixed up all values. The only similar bug we found in MOS was “OCI Application Fails With ORA-01483/ORA-01461 When Inserting VARCHAR2 Field From 12.2 Database Using Database Link To Lower Database Version. (Doc ID 2309285.1)”, but it shows different symptomps. Nevertheless, we have tried second workaround from this doc and it helped us. Workaround: |
Doc ID 2309285.1 |
Periodically never ending SQL_ID f1xfww55nj0xp with SYNC(on commit) and SMALL_R_ROW enabled | It seems that sometimes ctxsys.syncrn(:idxownid, :idxoname, :idxid, :ixpid, :rtabnm, :flg, :smallr) falls into infinite loop when you have ctx domain text indexes with SYNC(on commit) and SMALL_R_ROW option enabled Workaround: |
|
ORA-07445: exception encountered: core dump [keswxCurNbRows()+61] with CURSOR() and DBMS_XMLGEN.getXML() | Workaround: use the following parameters:
|
“Non-public” Bug 26696342 |
ORA-01722 on quering ctx_preference_values | After upgrade to Oracle 12.2.0.1 simple query “select * from ctx_preference_values” returns error ORA-01722: invalid number
details
If we look into the source of this view ctx_preference_values: create or replace view ctxsys.ctx_preference_values as select /*+ ORDERED INDEX(dr$preference_value) */ u.name prv_owner ,pre_name prv_preference ,oat_name prv_attribute ,decode(oat_datatype, 'B', decode(prv_value, 1, 'YES', 'NO'), nvl(oal_label, prv_value)) prv_value from sys."_BASE_USER" u ,dr$preference ,dr$preference_value ,dr$object_attribute ,dr$object_attribute_lov where prv_value = nvl(oal_value, prv_value) and oat_id = oal_oat_id (+) and oat_id = prv_oat_id and prv_pre_id = pre_id and pre_owner# = u.user#; and check PRV_VALUE from ctxsys.dr$preference_value: select pre_name, prv_pre_id,prv_value from ctxsys.dr$preference , ctxsys.dr$preference_value , ctxsys.dr$object_attribute where prv_pre_id = pre_id and oat_id = prv_oat_id and oat_datatype='B' and not regexp_like(prv_value,'^\d*$') / PRE_NAME PRV_PRE_ID PRV_VALUE --------------------------------- ---------- ---------- CTXSYS.JSONREST_GERMAN_LEXER 1098 YES CTXSYS.JSONREST_GERMAN_DIN_LEXER 1104 YES we can find that the root cause of the problem is the line with decode(prv_value, 1, ‘YES’, ‘NO’) Oracle tries to compare ‘YES’ with 1 in decode() and raises ORA-01722. [collapse]
Workaround: To add predicates to avoid these 2 preferences: select * from ctx_preference_values |
|
Wrong results with DETERMINISTIC functions in subquery factoring clause | Description from Eric van Roon Workaround: alter session set “_plsql_cache_enable”=false; |
Examples |