Re: dba_constraints.validated

I tried that command but it seems to validate all rows, that means I need a maintenance window to run the DDL, something I want to avoid. I have looked everywhere and I guess I will have to leave it in NOT VALIDATED state...

Thanks!

On Thu, Jan 7, 2016 at 7:45 PM, Stefan Knecht <knecht.stefan_at_gmail.com> wrote:

> Alter table can do that:
>
> SQL> create table t (x int, constraint t_pk primary key (x) enable
> novalidate);
>
> Table created.
>
> SQL> select constraint_name, status, validated from user_constraints where
> constraint_name='T_PK';
>
> CONSTRAINT_NAME
>
> --------------------------------------------------------------------------------
> STATUS VALIDATED
> -------- -------------
> T_PK
> ENABLED NOT VALIDATED
>
>
> SQL> alter table t modify constraint t_pk enable validate;
>
> Table altered.
>
> SQL> select constraint_name, status, validated from user_constraints where
> constraint_name='T_PK';
>
> CONSTRAINT_NAME
>
> --------------------------------------------------------------------------------
> STATUS VALIDATED
> -------- -------------
> T_PK
> ENABLED VALIDATED
>
> If there are any rows violating the constraint, the alter table will fail.
>
> Stefan
>
>
>
> On Fri, Jan 8, 2016 at 1:37 AM, Ls Cheng <exriscer_at_gmail.com> wrote:
>
>> Hi Rich
>>
>> The table was bulk-reloaded a year ago and the FKs were enabled using
>> novalidate option during a maintenance window to speed up the process but
>> after enable novalidate the state of VALIDATED stayed as "NOT VALIDATED". I
>> simply wonder if there is any DDL command to change it to VALIDATED.
>>
>>
>> Thanks
>>
>>
>> On Thu, Jan 7, 2016 at 4:22 PM, Rich J <rjoralist3_at_society.servebeer.com>
>> wrote:
>>
>>> On 2016/01/07 06:56, Ls Cheng wrote:
>>>
>>> I have some tables whose FK in dba_constraints.validated appears as "NOT
>>> VALIDATED". I guess it's because it was once enabled using novalidate
>>> clause.
>>>
>>> Is it possible to "change" this to VALIDATED without validating the
>>> constraints?
>>>
>>>
>>> I'm struggling as to why anyone would want to do this (outside of
>>> academic curiosity) or why any DB engine would ever allow marking an FK as
>>> validated when it hasn't been.
>>>
>>> It seems that one could trace a validation to generate the SQL to hack
>>> the dictionary into doing this, but I hopefully don't need to explain why
>>> that's a terrible idea.
>>>
>>> Just my $.02,
>>> Rich
>>>
>>>
>>>
>>
>>
>

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