Re: Temporary space needed to create a constraint

From: Jonathan Lewis <>
Date: Wed, 11 Jul 2012 12:39:28 +0100
Message-ID: <10D3796497CF4E7EA70B72AEF4DDD820_at_Primary>

What's your code to define the constraint and the existing index, and what's the execution plan you're seeing in the trace file ? And is the original table a simple heap table, or is it partitioned, and if partitioned was the index local or global ?

Create non-unique index;
add unique (or pk) constraint;

Shouldn't need to do anything other than walk the index - unless there's something funny going on with an attempt to do this online.

Have you tried

    add constraint enabled novalidate ?
    alter constraint validate ?


Jonathan Lewis

Author: Oracle Core (Apress 2011)

  • Original Message ----- From: "Peter Hitchman" <> To: "oracle-l" <> Sent: Wednesday, July 11, 2012 11:13 AM Subject: Re: Temporary space needed to create a constraint

Thanks for the comments. I forgot to say I think that the index I created is non-unique. After some more tracing I saw that Mark got it right, the SQL being run to ensure there are no duplicates is doing a hash join. I compared this to what happens when a unique index is created and the 10046 trace just showed a full table scan, where as the constraint creation runs some SQL that groups by the primary columns and has the predicate "having count(1) >1". I could not see in the trace for creating a unique index any SQL being run to ensure uniqueness, only the STAT line "SORT CREATE INDEX".

So now I am trying with a large HASH_AREA_SIZE and parallel slaves.


-- Received on Wed Jul 11 2012 - 06:39:28 CDT

Original text of this message