Hi,
As the PK is enabled, you have a index on it. If it's a unique index, then
validate constraint should be immediate, so maintenance window can be
short.
If it's not unique, that may be more difficult in 11g (in 12c you can
create additional unique and set the constraint to use it).
Anyway, do you have any reason to want it 'VALIDATED'? a 'novalidate rely'
constraint should be sufficient for query rewrite, optimizer, etc.
Regards,
Franck.
Franck Pachot | Senior Consultant & Oracle Technology Leader | Oracle
Certified Master and Oracle ACE
franck.pachot@xxxxxxxxxxxxxxxx
On Fri, Jan 8, 2016 at 8:40 PM Adric Norris <landstander668@xxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxxx> 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
