Re: Temporary space needed to create a constraint
- From: Kellyn Pot'vin <kellyn.potvin@xxxxxxxxx>
- To: "mwf@xxxxxxxx" <mwf@xxxxxxxx>, 'oracle-l' <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 10 Jul 2012 15:36:18 -0700 (PDT)
I'm just going to chime in here... Didn't the original poster state that this
process was being performed in parallel? I would inspect the slave/parallel
processes for temp usage on each, which should show the sort in the explain
plan as well, as I would expect sorting to occur with the parallel processes as
they "knit" the results back together, (I know, this is my term for this step,
please do not knock me for it... :))
Kellyn Pot'Vin
Senior Technical Consultant
Enkitec
DBAKevlar.com
________________________________
From: Mark W. Farnham <mwf@xxxxxxxx>
To: 'oracle-l' <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, July 10, 2012 4:16 PM
Subject: RE: Temporary space needed to create a constraint
Yeah, what JL wrote.
Could you send the DDL and version information?
Something is not making sense.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jonathan Lewis
Sent: Tuesday, July 10, 2012 5:35 PM
To: oratune@xxxxxxxxx; mark.powell2@xxxxxx; oracle-l
Subject: Re: Temporary space needed to create a constraint
But if there is a pre-existing index that contains all the data needed to
enforce the constraint Oracle shouldn't need to do any sorting, it need only
do a full scan of the index to check that there are no duplicates.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
----- Original Message -----
From: "David Fitzjarrell" <oratune@xxxxxxxxx>
To: <mark.powell2@xxxxxx>; "oracle-l" <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, July 10, 2012 9:31 PM
Subject: Re: Temporary space needed to create a constraint
Which is exactly what he's done -- the constraint creation runs out of temp
space even though no index is being created. The sort segment outgrows the
available temp space and then errors out. I can guess that Oracle is
ordering the keys to verify no duplicates exist before creating and
enforcing the primary key, but I can't prove that on my small playground
database.
David Fitzjarrell
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
