RE: ORA-02294 only on some parallel PK validates

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 21 May 2014 18:05:56 -0400
Message-ID: <03a501cf7540$d8c6d5e0$8a5481a0$_at_rsiz.com>



It is unclear to me why you do not proceed with the 15 indentified tables non-parallel, if that works repeatably.

With so many objects available to run in separate threads, it is unclear to me that parallel is an advantage for any of this process.

In fact, if you decide to go that way, I would expect a significant reduction in the overall elapsed time. For tables that will fit in the buffer cache, you might want to run a scanner to get nice clean blocks in there just before you unleash the index creates for that table, with a thread for each index create on a cached table.

If overall elapsed time is not an issue, then making the 15 problematic tables non parallel is also not an issue.

Then you can save your SR processing for a post mortem.

Good luck,

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich Jesse
Sent: Wednesday, May 21, 2014 4:59 PM
To: oracle-l_at_freelists.org
Subject: ORA-02294 only on some parallel PK validates

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

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Thu May 22 2014 - 00:05:56 CEST

Original text of this message