Re: estimate progress of constraint creation?

From: Adric Norris <landstander668_at_gmail.com>
Date: Tue, 24 Jan 2012 15:30:00 -0600
Message-ID: <CAJueESomC6utmhEKS8TWSBew_0KeSS93Lx51B+tc2aRdg4+tSw_at_mail.gmail.com>

Oh, that's a nice one... I'll definitely hang onto it for future reference. Thanx!
I don't think it will help in this specific case, however, because what needs to occur next is a plethora of partition exchanges for the table in question. I'm pretty sure that would still be blocked by the constraint validation, although normal DML access would be fine. Still, it's a very nice tip. :)

On Tue, Jan 24, 2012 at 14:17, Bobak, Mark <Mark.Bobak_at_proquest.com> wrote:

> Ah, but you can do something like this:
> SQL> create table t(c1 number(4) not null, c2 number(4));
>
> Table created.
>
> SQL> alter table t modify(c2 not null enable novalidate);
>
> Table altered.
>
> SQL> desc t
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> C1 NOT NULL NUMBER(4)
> C2 NUMBER(4)
>
> SQL> select 'alter table t modify constraint '||constraint_name||' enable
> validate;' from user_constraints where table_name = 'T' and validated='NOT
> VALIDATED';
>
> 'ALTERTABLETMODIFYCONSTRAINT'||CONSTRAINT_NAME||'ENABLEVALIDATE;'
>
> -------------------------------------------------------------------------------
> alter table t modify constraint SYS_C0084060 enable validate;
>
> SQL>
> SQL> alter table t modify constraint SYS_C0084060 enable validate;
>
> Table altered.
>
> SQL> desc t
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> C1 NOT NULL NUMBER(4)
> C2 NOT NULL NUMBER(4)
>
> The benefit being, validating a constraint that's already enabled won't
> take and hold nasty table locks. Once constraint is enabled as novalidate,
> you can start processing data.
>
> Hope that helps,
>
> -Mark
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 24 2012 - 15:30:00 CST