RE: Temporary space needed to create a constraint
Date: Wed, 11 Jul 2012 12:38:48 +0000
Yes, you are right. I do not know why but I misremembered the middle paragraph. Which would leave either attempting to add the constraint with no validate or dropping the index and re-creating the index as part of the create of the pk constraint.
From: David Fitzjarrell [mailto:oratune_at_yahoo.com]
Sent: Tuesday, July 10, 2012 4:31 PM
To: Powell, Mark; oracle-l
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.
From: "Powell, Mark" <mark.powell2_at_hp.com<mailto:mark.powell2_at_hp.com>>
To: oracle-l <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>>
Sent: Tuesday, July 10, 2012 2:20 PM
Subject: RE: Temporary space needed to create a constraint
I do not know the answer to your question but to create the constraint I would try to create the index then alter the table to create the PK using the already built index.
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] On Behalf Of Peter Hitchman Sent: Tuesday, July 10, 2012 11:59 AM
Subject: Temporary space needed to create a constraint
Oracle Enterprise Edition: 184.108.40.206.5
OS: RHL. Hi,
I have a table with 1.8 billion rows, on which I created a non unique index and now I am trying to add in a primary key constraint using that index. But I keep running out of temporary space. I assumed that the space needed to create the constraint would be the same as needed to create the index (which was around 160GB), but it appears that it needs double that. I did a small test and found that the amount of temp space needed to create a PK in this circumstance was double that needed to create the index.
In my latest attempt I tried to uses a 1GB sort_area_size and it ran for longer but in the end exhausted alll the disk space I have.
Has anyone else experienced this and if so why does creating the constraint use so much more temp space than creating an index on the same columns?
http://www.freelists.org/webpage/oracle-l Received on Wed Jul 11 2012 - 07:38:48 CDT