Re: estimate progress of constraint creation?
- From: Adric Norris <landstander668@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 24 Jan 2012 15:30:00 -0600
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@xxxxxxxxxxxx> 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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
