Temporary space needed to create a constraint

  • From: Peter Hitchman <pjhoraclel@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 10 Jul 2012 16:58:52 +0100

Oracle Enterprise Edition:

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

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?


Other related posts: