Why I'll always have something to eat in my plate, while working on Oracle ...
Yesterday - all my troubles seemed so far away -
I solved a problem we had for several weeks when an application program didn't work on my PRE-Prod environment but was OK on PRODuction.
Both are 10.2.0.5 Kernel on AIX 5.3. Here it is :
1/ The Issue
1.1 first, Event 10046
After tracing the Oracle session, I found the difference between those 2 environments
the following DML failed in PRE :
update EVO_CONFIG40000.C_WIZARD set "XML" = :XML
where EVO_CONFIG40000.C_WIZARD.WIZARD_ID = (Select WIZARD_ID
From EVO_CONFIG40000.C_WIZARD_REFERENCE
Where WIZARD_GUID = :OraParam_1 OR WIZARD_GUID = :OraParam_2) AND VERSION = :OraParam_3
It returned one modified row in PROD and 0 in PRE.
1.2 client point of view
Thanks to my SYSTEM colleagues, I got the error logged by client :
ORA-01461: can bind a LONG value only for insert into a LONG column
2/ The Cause
After a while searching through Metallica, I discovered that we hit the bug # 9762767
Bug 9762767: ORA-1461 UPDATING CLOBS AGAINST 11.2.0.1 DB WHEN DML CONTAINS SUB-SELECT STMT
Even if noted as 11.2 version, the bug appeared from the 10.2.0.5 on ;
it have been introduced due to the fix for a different bug :
bug 10.2.0.4 # 6990305 -- WRONG COSTING AND PLAN AFTER UPGRADE TO 10.2.0.4.0
3/ The Solution
Now, what ? Metallica excerpt :
The fix for unpublished Bug 9255996 will be included in the 12.1 database when it is released.
I've never heard about the 12.1 version until now ...
Fortunately, we may inhibit the bug fix that cause our bug at SYSTEM or SESSION level :
alter system set "_fix_control"='6990305:OFF';
alter session set "_fix_control"='6990305:OFF';
4/ Why was it OK in Production ?
These parameters were not in sync :
PRE-Prod optimizer_features_enable UNSET = 10.2.0.5 ( = to v$version "Oracle engine" )
PROD optimizer_features_enable = 10.2.0.3
In Production, the Optimizer is behaving without having any idea about all the mess encountered in PRE !
Note
See also: Complex CLOB Updates Fail With ORA-01461 After RDBMS Upgraded To 11.2.0.1 [ID 1222296.1]
PS : About the COMPATIBLE parameter in 10g - summary
It can be set to anything from 9.2.0 to 10.2.0.X, the level of your Oracle release.
If UNSET, it is equal to 10.2.0, which means 10.2.0.0 ;
it may be set to an higher level later, once sure that the database wouldn't subsequently be downgraded.
optimizer_features_enable is independent from the COMPATIBLE parameter and ranges from 8.0.0 to 10.2.0.X.
If UNSET, it is equal to 10.2.0.X ( v$version ).
