I upgraded to 11.2 in production some days after it was released in September 2010.
Since them I did hit a number of bugs, got some of them fixed with Oracle Support and are still working with them on a number of others.
The most serious for me ( because my data was not validated anymore in a number of SQLs – and I care about my data ) I wrote about on
https://marogel.wordpress.com/2011/03/27/prevent-validation-of-check-constraints-in-11-2-0-2/
Thanks t
Timo Raitalaakso I was informed about an available patch 9285259 for “Bug 9285259: MERGE IGNORES CHECK CONSTRAINT”
It did install without issues on test-db and I verified successfully that the software was really patched, I could not cheat again with merge.
So today (Sunday) I patched production in 5 minutes. Good news: my constraints are checked again !
Bad news: I now have to validate each of them manually – did my data run into inconsistencies ?
How to achive that ? No word on Oracle Support about that. Seems to me, they don’t really care much about the consistency of the data of their clients.
My first idea was something like this (pseudo-code)
for c in (
select owner, table_name, search_condition, constraint_name
from dba_constraints
where constraint_type='C'
) loop
execute immediate
'select count(*) from ' ||
c.owner || '."' || c.table_name ||
'" where not (' || c.search_condition || ') '
into n;
if n > 0 then
dbms_output.put_line(
c.owner || ', ' || c.table_name || ', ' || c.constraint_name || ': ' ||
n || 'exceptionally rows - you may want to have a deeper look onto these');
end if;
end loop;
However, the optimizer of course takes a shortcut – using the constraint:
11.2.0.2 > create table t ( i int check( i != 3 ));
Table created.
11.2.0.2 > insert into t values(2);
1 row created.
11.2.0.2 > merge into t
2 using ( select rowid r from t) s
3 on (t.rowid = s.r)
4 when matched then
5 update set i = 3;
1 row merged.
11.2.0.2 > set autotr on explain
11.2.0.2 > select i from t where not (i != 3);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1322348184
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| T | /1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - filter("I"=3)
Note
-----
- dynamic sampling used for this statement (level=2)
See the
” 1 – filter(NULL IS NOT NULL)”
using the check constraint.
So my second idea was to use lnnvl :
11.2.0.2 > select i from t where lnnvl(i != 3);
I
----------
3
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 13 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(LNNVL("I"<>3))
Note
-----
- dynamic sampling used for this statement (level=2)
well, this one seems to work – for this case
But it doesn’t work for all cases – see:
11.2.0.2 > create table s( i int check ( i in (3, 4) ));
Table created.
11.2.0.2 > insert into s values(3);
1 row created.
11.2.0.2 > merge into s
2 using (select rowid r from s) p
3 on (p.r = s.rowid)
4 when matched then update
5 set i = 2;
1 row merged.
11.2.0.2 > commit;
Commit complete.
11.2.0.2 > select i from s;
I
----------
2
11.2.0.2 > select search_condition, validated, status from user_constraints where table_name='S';
SEARCH_CONDITION VALIDATED
STATUS
-------------------------------------------------------------------------------- -------------
--------
i in (3, 4) VALIDATED
ENABLED
11.2.0.2 > select * from s where lnnvl(i in (3, 4));
select * from s where lnnvl(i in (3, 4))
*
ERROR at line 1:
ORA-13207: incorrect use of the [LNNVL] operator
so now, my question is still holds:
how can I validate all my check constraints manually without using clever optimizer ?
Seems to me, I have to disable / enable each of them.
Enabling with validate of course.
And not in production of course, but on a clone DB.
Update on Monday:
ok, tests show there is no need to disable them before call enable validate.
So, the following script to validate all in-doubt check-constraints of a list of owners suffices:
create table exceptions(row_id urowid,
owner varchar2(30),
table_name varchar2(30),
constraint varchar2(30));
declare
check_cons_violated exception;
pragma exception_init(
check_cons_violated,
-2293 -- "cannot validate (%s.%s) - check constraint violated"
);
vers_obj_cant_be_altered exception;
pragma exception_init(
vers_obj_cant_be_altered,
-20171 -- "WM error: Versioned objects cannot be altered."
);
begin
for c in (
select *
from dba_constraints
where constraint_type = 'C'
and owner in (....)
) loop
begin
execute immediate
'alter table "' || c.owner ||
'"."' || c.table_name ||
'" enable validate constraint "' ||
c.constraint_name ||
'" exceptions into exceptions';
exception when check_cons_violated or vers_obj_cant_be_altered then null;
end;
end loop;
end;
/
select * from exceptions;
Sokrates on Oracle 