Oracle SQL
  • LICENSE

Oracle issues after upgrade to 12.2

Posted on November 24, 2017 by Sayan Malakshinov Posted in 12.2, bug, oracle 2 Comments

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:
set “_qkslvc_extended_bind_sz” to 0 and bounce the database.

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:
Recreate indexes as TRANSACTIONAL Near real-time indexes with SYNC(every…) option.

ORA-07445: exception encountered: core dump [keswxCurNbRows()+61] with CURSOR() and DBMS_XMLGEN.getXML() Workaround:
use the following parameters:

  • “_optimizer_use_feedback”=false
  • “_optimizer_gather_feedback”=false
  • “_optimizer_dsdir_usage_control”=0
  • “_iut_enable”=false
“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’)
and values ‘YES’ for preferences ‘CTXSYS.JSONREST_GERMAN_LEXER’, ‘CTXSYS.JSONREST_GERMAN_DIN_LEXER’.

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
where prv_preference not in (‘CTXSYS.JSONREST_GERMAN_LEXER’,’CTXSYS.JSONREST_GERMAN_DIN_LEXER’);

Wrong results with DETERMINISTIC functions in subquery factoring clause Description from Eric van Roon
Workaround:
alter session set “_plsql_cache_enable”=false;
Examples
12.2.0.1 bind variable bug deterministic functions oracle troubleshooting undocumented oracle
« Triggers and Redo: changes on 12.2
Bug with integer literals in PL/SQL »
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL