Re: estimate progress of constraint creation?
- From: Adric Norris <landstander668@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 24 Jan 2012 13:22:55 -0600
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
Other related posts:
