Re: dba_constraints.validated

I run a test just in case to prove there is no lock when the constraint is already enabled and validation is fired and it does not lock as you have suggested.

Thanks!

On Fri, Jan 8, 2016 at 8:39 PM, Adric Norris <landstander668_at_gmail.com> wrote:

> It's not nearly as bad as it sounds. Constraint validation, assuming that
> the constraint is already enabled (i.e. enforced for new/updated data), is
> actually a read-only operation which doesn't block DML. I've done this
> *lots* of time on some very active 11.2.0.x database tables, when we had
> to make structural changes in an incremental manner in order to avoid
> downtime.
>
>
> On Thu, Jan 7, 2016 at 2:12 PM, Ls Cheng <exriscer_at_gmail.com> wrote:
>
>> Hi Stephan
>>
>> 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
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>
>
> --
> "In the beginning the Universe was created. This has made a lot of people
> very angry and been widely regarded as a bad move." -Douglas Adams
>

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