Re: ENABLE NOVALIDATE behaviour bug

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 29 Jun 2004 23:26:24 +0100
Message-ID: <001901c45e28$2051a1a0$7102a8c0@Primary>

One thought about adding a column with a default: the table has to be locked while every row is updated, and if the update fails, you have a massive rollback.

If this is not something you can spare the time for, you might still want to do something like the following:

    add column
    add constraint disable novalidate
    update the column where null in batches - committing     add the default
    enable the constraint novalidate
    update any columns still null
    validate the constraint

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st

Original Message ----- From: "Mark Richard" <mrichard_at_transurban.com.au> To: <oracle-l_at_freelists.org> Sent: Tuesday, June 29, 2004 11:15 PM Subject: Re: ENABLE NOVALIDATE behaviour bug

Thanks to Tanel and Richard for explaining this. I know realise that I misunderstood the meaning of "novalidate" - I was hoping it might mean "trust me Oracle, I know it to be true" but it can only mean "from today on, enforce this - who knows about the past". I should have realised that.

I'll take Tanel's advice of creating the column with a default. This is yet another area I wasn't sure about... I wasn't positive that a default value would be applied to existing rows since adding a default clause to a pre-existing column doesn't do this. So I've asked one question and received two answers - Well done and thankyou.

Regards,

Mark.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Tue Jun 29 2004 - 17:23:12 CDT