RE: estimate progress of constraint creation?

From: Bobak, Mark <Mark.Bobak_at_proquest.com>
Date: Tue, 24 Jan 2012 15:17:30 -0500
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F87472E36_at_AAPQMAILBX02V.proque.st>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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_at_evdbt.com> 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_at_dwprod1> create table t (

  2     c1 number(4) not null,
  3     c2 number(4)

  4 );

Table created.

TEST_at_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_at_dwprod1> alter table t
  2 modify (c2 not null enable novalidate);

Table altered.

TEST_at_dwprod1> desc t

 Name                 Null?    Type
 -------------------- -------- --------------
 C1                   NOT NULL NUMBER(4)
 C2                            NUMBER(4)

TEST_at_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_at_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_at_dwprod1> select constraint_name from user_constraints

  2     where table_name = 'T'
  3       and validated = 'NOT VALIDATED';

CONSTRAINT_NAME

---

SYS_C00601485 TEST_at_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.

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




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

Received on Tue Jan 24 2012 - 14:17:30 CST