ORA-02294 only on some parallel PK validates
- From: "Rich Jesse" <rjoralist3@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 21 May 2014 15:59:20 -0500 (CDT)
Hey all,
In 11.2.0.3.8 EE on AIX, we're moving data around for a migration. Here's
the high-level of the script:
1) Drop all PKs and indexes in target DB.
2) Datapump import 4600+ tables from a previous dpexp, data only.
3) Run 750+ really ugly UPDATEs, TRUNCATE a few tables, etc.
4) Recreate all indexes/PKs
4a) ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;
4b) ALTER TABLE ttt PARALLEL (DEGREE 8) for "large" tables.
4c) Recreate all 9300+ indexes
4d) Recreate all 4600+ PKs w/NOVALIDATE (FKs not used)
4e) ALTER TABLE...VALIDATE for all 4600+ PKs.
4f) Reset all 9300+ indexes to NOPARALLEL
The parallelism and separate PK validation on step 4 is necessary for
performance, and it generally works very well.
However, 15 of the PKs failed on the ALTER...VALIDATE with "ORA-02294:
cannot enable (MYUSER.MYTBL_PK) - constraint changed during validation".
Recreating them after the script completed w/o parallel worked fine.
MOS has an incorrect message for the ORA-2294 error (feedback on the article
didn't seem to work, either). And searching the nets just comes up with
"someone else is altering the PK, too". Not in this case. There were no
other connections to the DB at the time, verified by listener logs and
v$session reports.
I suspect parallel issues, but was hoping someone else ran into this, as I
can't afford the time to deal with an SR now.
Anyone?
Rich
--
//www.freelists.org/webpage/oracle-l
Other related posts:
