RE: estimate progress of constraint creation?
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "landstander668@xxxxxxxxx" <landstander668@xxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 24 Jan 2012 15:17:30 -0500
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
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Adric Norris
Sent: Tuesday, January 24, 2012 2:23 PM
To: oracle-l
Subject: Re: estimate progress of constraint creation?
On Tue, Jan 24, 2012 at 11:35, Tim Gorman <tim@xxxxxxxxx> wrote:
> How about ENABLE NOVALIDATE and then create queries to validate them
> yourself?
>
The data is already known to be good, so we're OK from that perspective.
Unfortunately ENABLE NOVALIDATE doesn't behave, or at least *look*, the same.
For example:
TEST@dwprod1> create table t (
2 c1 number(4) not null,
3 c2 number(4)
4 );
Table created.
TEST@dwprod1> desc t
Name Null? Type
-------------------- -------- --------------
C1 NOT NULL NUMBER(4)
C2 NUMBER(4)
Now we'll add a NOT NULL constraint to the second column in ENABLE NOVALIDATE
mode.
TEST@dwprod1> alter table t
2 modify (c2 not null enable novalidate);
Table altered.
TEST@dwprod1> desc t
Name Null? Type
-------------------- -------- --------------
C1 NOT NULL NUMBER(4)
C2 NUMBER(4)
TEST@dwprod1> insert into t values (1, NULL); insert into t values (1, NULL)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TEST"."T"."C2")
The newly-added constraint is indeed enforced for new data... so far, so good.
But wait:
TEST@dwprod1> desc t
Name Null? Type
-------------------- -------- --------------
C1 NOT NULL NUMBER(4)
C2 NUMBER(4)
Hmmm, still showing as nullable... that's bound to cause confusion later on.
Perhaps adding RELY into the mix will allow it to be handled normally?
TEST@dwprod1> select constraint_name from user_constraints
2 where table_name = 'T'
3 and validated = 'NOT VALIDATED';
CONSTRAINT_NAME
------------------------------
SYS_C00601485
TEST@dwprod1> alter table t
2 modify constraint SYS_C00601485 rely;
alter table t
*
ERROR at line 1:
ORA-25127: RELY not allowed in NOT NULL constraint
Unfortunately, this puts us back to simply needing to wait for the (very
lengthy) normal constraint creation process to complete.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
