Sokrates on Oracle

Prevent Validation of Check Constraints in 11.2.0.2

Posted by Matthias Rogel on 27. March 2011

11.2.0.2 has a great new “feature”: you can disable the validation of a check contraint during an update !

how does it work ?
it is a bit like magic: when an update violates a check constraint, just use a merge-statement to update the table.

See:

sokrates@11.2.0.2 > create table oops ( i int primary key, n number constraint ch_n_sm_5 check( n < 5 ));

Table created.

sokrates@11.2.0.2 > insert into oops values(1, 2);

1 row created.

sokrates@11.2.0.2 > insert into oops values(2, 4.5);

1 row created.

sokrates@11.2.0.2 > commit;

Commit complete.

sokrates@11.2.0.2 > update oops set n = n * 7 where i = 1;

update oops set n = n * 7 where i = 1
*
ERROR at line 1:
ORA-02290: check constraint (SOKRATES.CH_N_SM_5) violated

so far, so good. now the trick:

sokrates@11.2.0.2 > merge into oops using (select 1 d from dual) d on (d.d = oops.i) when matched then update set n = n * 7;

1 row merged.

sokrates@11.2.0.2 > REM oops !!!

sokrates@11.2.0.2 > select i, n from oops;

I        N
———- ———-
1       14
2      4.5

sokrates@11.2.0.2 > commit;

Commit complete.

Now, isn’t that a great new feature ?

What does Oracle say ?

Bug 9285259: MERGE IGNORES CHECK CONSTRAINT

says:


Fixed in Product Version 12.1

WORKAROUND:
-----------
none

So, in my opinion, if you use “merge” to update rows, you have a real problem and probably your best decision could be “don’t use 11.2 at all, wait until they have fixed that behaviour in 12.1″

Remarks.

I can reproduce the Bug on 11.2.0.2 on Linux x86-64

According to the metalink note mentioned above, it affects also 11.2.0.1, which I haven’t checked.

Furthermore, I don’t know if it’s only specific to that platform.

About these ads

11 Responses to “Prevent Validation of Check Constraints in 11.2.0.2”

  1. Certainly a remarkable bug.

    For your information, there is a workaround described further down in the bug description: Add a INSERT or DELETE clause to the MERGE statement.

    Also, there is already an one-off patch for 11.2.0.2 Linux x64 available and backports for other versions / platforms can be requested

    Randolf

  2. Dom Brooks said

    Oooh – that’s a bad one.

    Dominic

  3. […] blog for articles about some of the nastier, and sometimes unbelievable, bugs in the Oracle code. Here’s one (that’s just about forgivable in terms of failing to test) published by Sokrates a couple of […]

  4. http://rafudb.blogspot.com/2011/03/merge-ignores-check-constraint.html the on off patch is online installable. No downtime required.

  5. hourim said

    mhouri@mhouri> select * from v$version;

    BANNER
    ——————————————————————————–
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
    PL/SQL Release 11.2.0.1.0 – Production
    CORE 11.2.0.1.0 Production
    TNS for 32-bit Windows: Version 11.2.0.1.0 – Production
    NLSRTL Version 11.2.0.1.0 – Production

    mhouri@mhouri> merge into oops using (select 1 d from dual) d on (d.d = oops.i) when matched then update set n = n * 7;

    1 ligne fusionnée.

    mhouri@mhouri> commit;

    Validation effectuée.

    mhouri@mhouri> spool off;

  6. […] Timo Raitalaakso I was informed about an available patch 9285259 for “Bug 9285259: MERGE IGNORES CHECK CONSTRAINT” […]

  7. […] http://marogel.wordpress.com/2011/03/27/prevent-validation-of-check-constraints-in-11-2-0-2/ […]

  8. […] I noticed that since applying Patch 9285259 we did hit no ORA-08103 anymore […]

  9. I am getting lot of problems due to this and waiting for the oracle release – where they promised to fix this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: